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

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

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

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 wrote:

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

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

Thanks Dave;
You assumed correctly.
Jim

"Dave Peterson" wrote:

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 wrote:

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