Excel: Copying Data to another worksheet

Hello, The aim of this is to search through a range of cells for particular status and then copy certain corresponding information over to another sheet (sheet 2) if the correct status is achieved. This works when I use it to copy the data into the same worksheet and it mysteriously worked once when I asked it to copy to sheet 2 once but ever since then it wont work. The debugger is showing an error on the "Status.Copy..." line. Where am I going wrong?

Dim SearchRange As Range
Dim Status As Range
Set SearchRange = Worksheets("Sheet1").Range("D2", Range("D1").End(xlDown))
For Each Status In SearchRange
If Status = "Active" Or Status = "Lapsing" Then
Status.Copy Destination:=Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0)
Status.Offset(0,-1).Copy Destination:=Worksheets("Sheet2").Range("B1").End(xlDown).Offset(1, 0)
End If
Next Status
End Sub
Thank you

This question generated 12 answers. To proceed to the answers, click here.

This thread is current as of January 26, 2018.

For more resources for Microsoft Excel