Copying Data to another worksheet

CC27RR

New Member
Joined
Jan 26, 2018
Messages
7
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?

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi & welcome to the board.
What error message did you get?
And do you have sheet protection on either sheet?
 
Upvote 0
See if this does what you want.

Code:
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
 
Upvote 0
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
 
Upvote 0
There's nothing obviously wrong with your code, but how about
Code:
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
 
Upvote 0
See if this does what you want.

Code:
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
 
Upvote 0
May sound a daft question, but do you have a sheet called "Sheet2"?
 
Upvote 0
There's nothing obviously wrong with your code, but how about
Code:
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
 
Upvote 0
Simple error in both your original code & mine, in that the search range was not properly set. It should be
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top