Why am I getting R/T 1004 - Copy method of Rng class failed?

[Jim May]

Sub tester()
For Each cell In Sheets("Data").Range("A2:A10")
If cell.Value = "Stuff" Then
Cells(cell.Row, 7).Copy _
Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) + 1
End If
Next cell
End Sub

 

[Dave Peterson]

Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) + 1

will try to add 1 to whatever is in that last used cell.

If you were using a LastRow kind of thing, you could use:
LastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).row + 1

But I bet you just want to drop down a row.

Cells(cell.Row, 7).Copy _
Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).offset(1,0)

 

[Jim May]

Thanks Dave;
You assumed correctly.
Jim

Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) + 1

will try to add 1 to whatever is in that last used cell.

If you were using a LastRow kind of thing, you could use:
LastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).row + 1

But I bet you just want to drop down a row.

Cells(cell.Row, 7).Copy _
Destination:=Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).offset(1,0)