Error trying to loop through cells and copy to another sheet

[sharky12345]

I'm trying to loop through the non-blank cells in column A of a worksheet and then copy each of those values to another worksheet by using this;

'Import items
Set ws = DestFile.Sheets("HandoverSummary")
With DestFile.Sheets("HandoverItems")
Lastrow = Range("A65536").End(xlUp).Row
For Each cell In Range("A2:A" & Lastrow)
If cell <> "" Then
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1).Value = .Range("A" & cell).Value
End If
Next
End With

It fails on this line and I don't know why;

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

I know that I can use a straight copy and paste but I will need to avoid that because once I've got this working I will need to add an IF statement if the value in the cell is a certain string.

 

[Fluff]

If you're working with both old & new versions of xl try

iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

That said there are a few other potential problems. Try

Set WS = DestFile.Sheets("HandoverSummary")
With DestFile.Sheets("HandoverItems")
   LastRow = .Range("A65536").End(xlUp).Row
   For Each cell In .Range("A2:A" & LastRow)
      If cell <> "" Then
         irow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
         WS.Cells(irow, 1).Value = cell.Value
      End If
   Next
End With

 

[Joe4]

It also appears that you are using "With" a bit oddly.
Typically, you would see a structure like:

With DestFile.Sheets("HandoverItems")
    Lastrow = .Range("A65536").End(xlUp).Row
    ...
End With

Note the period in front of Range, indicating that it goes with the WITH statement.
Otherwise, it really serves no purpose to use WITH.

 

[sharky12345]

Thanks guys - I'll have a play with your suggestions.