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

 

[Fluff]

Hi & welcome to the board.
What error message did you get?
And do you have sheet protection on either sheet?

 

[Scott T]

See if this does what you want.

Sub cc27rr()
Dim SearchRange As Range
Dim Status As Range
Dim lr1 As Long
Dim lr2 As Long
lr1 = Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
Set SearchRange = Worksheets("Sheet1").Range("D2:D" & lr1)
For Each Status In SearchRange
    If UCase(Status) = "ACTIVE" Or UCase(Status) = "LAPSING" Then
        lr2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        Status.Copy Destination:=Worksheets("Sheet2").Range("A" & lr2 + 1)
        Status.Offset(0, -1).Copy Destination:=Worksheets("Sheet2").Range("B" & lr2 + 1)
    End If
Next Status
End Sub

 

[CC27RR]

Hi & welcome to the board.
What error message did you get?
And do you have sheet protection on either sheet?

thank you
the error message I am getting is:
Run time error 1004
Application-defined or object-defined error

And there is no worksheet protection on this

 

[Fluff]

There's nothing obviously wrong with your code, but how about

Sub chk()
   Dim SearchRange As Range
   Dim Status As Range
   
   Set SearchRange = Worksheets("Sheet1").Range("D2", 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

 

[CC27RR]

See if this does what you want.

Sub cc27rr()
Dim SearchRange As Range
Dim Status As Range
Dim lr1 As Long
Dim lr2 As Long
lr1 = Sheets("Sheet1").Cells(Rows.Count, 4).End(xlUp).Row
Set SearchRange = Worksheets("Sheet1").Range("D2:D" & lr1)
For Each Status In SearchRange
    If UCase(Status) = "ACTIVE" Or UCase(Status) = "LAPSING" Then
        lr2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        Status.Copy Destination:=Worksheets("Sheet2").Range("A" & lr2 + 1)
        Status.Offset(0, -1).Copy Destination:=Worksheets("Sheet2").Range("B" & lr2 + 1)
    End If
Next Status
End Sub

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

 

[Fluff]

May sound a daft question, but do you have a sheet called "Sheet2"?

 

[CC27RR]

There's nothing obviously wrong with your code, but how about

Sub chk()
   Dim SearchRange As Range
   Dim Status As Range
   
   Set SearchRange = Worksheets("Sheet1").Range("D2", 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

Yes this works however only when run from sheet 1. If I run it from sheet 2. Ultimately I need this to be run from sheet 2. So from sheet 2 you are looping through a range in sheet 1 and then copying the relevant data through.

So the issue here was that I did not set up the range in the destination correctly?

Thank you very much for your help

 

[CC27RR]

May sound a daft question, but do you have a sheet called "Sheet2"?

yes there is sheet1, sheet2 and sheet3

 

[Fluff]

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