Copying Data to another worksheet

[CC27RR]

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

 

[Scott T]

Yes that has worked but I don't quite understand it

Why did my original code not work?
What is the relevance of putting the statuses in capital letters?
What is the purpose of amending the searchrange variable I set?

Thank you I appreciate your help

I believe you were getting the error because if there was nothing in A2:A end of column. then you would be at the last row of the sheet and then try to offset down one row which does not exist.

Status.Copy Destination:=Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0)

In your original code if someone entered active your code would say "active"<>"Active" because of the different case by using ucase to make the status all upper case and comparing it all upper case the code will still see them as =.

Since the range in red did not have a sheet specified it would use the active sheet. So this would only work when sheet1 was the active sheet

Set SearchRange = Worksheets("Sheet1").Range("D2", [COLOR=#ff0000]Range("D1")[/COLOR].End(xlDown))

you could also use

Set SearchRange = Worksheets("Sheet1").Range("D2", Worksheets("Sheet1").Range("D1").End(xlDown))

 

[CC27RR]

Simple error in both your original code & mine, in that the search range was not properly set. It should be

Sub chk()
   Dim SearchRange As Range
   Dim Status As Range

   Set SearchRange = Worksheets("Sheet1").Range("D2", [COLOR=#ff0000]Worksheets("Sheet1").[/COLOR]Range("D" & Rows.Count).End(xlUp))
   
   For Each Status In SearchRange
      If Status = "Active" Or Status = "Lapsing" Then
         Status.Copy Destination:=Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
         Status.Offset(0, -1).Copy Destination:=Worksheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1)
      End If
   Next Status

End Sub

I believe you were getting the error because if there was nothing in A2:A end of column. then you would be at the last row of the sheet and then try to offset down one row which does not exist.

Status.Copy Destination:=Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0)

In your original code if someone entered active your code would say "active"<>"Active" because of the different case by using ucase to make the status all upper case and comparing it all upper case the code will still see them as =.

Since the range in red did not have a sheet specified it would use the active sheet. So this would only work when sheet1 was the active sheet

Set SearchRange = Worksheets("Sheet1").Range("D2", [COLOR=#ff0000]Range("D1")[/COLOR].End(xlDown))

you could also use

Set SearchRange = Worksheets("Sheet1").Range("D2", Worksheets("Sheet1").Range("D1").End(xlDown))

thanks for your help, much appreciated