CODEWITHSUNDEEP
excelvba
Anony123
Anony123

Reputation: 55

Copying a row and pasting it at the bottom of another sheet

I want to copy row 2 from a sheet and paste it to the last empty row in another sheet (Sheet2). So far, this is what I came up with: 

Sub testj()

Rows(2).Copy
Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0).EntireRow.Insert

End Sub

This gives the error: subscript out of range. I can't seem to find out what's wrong with it.

Upvotes: 0

Views: 926

Answers (1)

VBasic2008
VBasic2008

Reputation: 54767

Renamed Worksheet?!

When you run your code and there is no worksheet with the name "Sheet2" (in the Tab):

Run-time error: '9' Subscript out of range

When you have no data from A2 to the bottom in worksheet with the name "Sheet2":

Run-time error '1004': Application-defined or object-defined error

To conclude: You might have accidentally renamed "Sheet2". To avoid this you can use the code name of the sheet like this:

Rows(2).Copy
Sheet2.Range("A1").End(xlDown).Offset(1, 0).EntireRow.Insert

Now you can rename the sheet in the Tab as you wish.

The Last Used Cell in a Column

If you are 100% sure that there is data in at least the first two rows and your data is contiguous (no empty cells), your code works fine. If the data in the column is not contiguous, the first empty cell at which it stops won't be the last. So you will overwrite existing data.

Therefore the last cell in a column is mostly calculated from the bottom like this:

Rows(2).Copy
Sheet2.cells(Rows.count,"A").End(xlUp).Offset(1, 0).EntireRow.Insert

This has the limitation that it will never copy into Row 1, but since you usually have headers there it is fine.

A way without limitation is using the Find Method.

Upvotes: 1

Related Questions