Find Rows and Copy to new worksheet

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
1,004
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
Hi All,
I need to find a unique cell in my summary worksheet and then copy to a new worksheet
I have had a go to try and do this although the header row (row 1) copies fine, although the rest of the data pastes in the last row 1 of the spreadsheet. can anyone help?
The unique data is (G512, G515, G521, G532, S313, VL362). as this is a daily report, the unique data may not always be in the summary worksheet.

Code:
Sub VIC_Report()
    Sheets("Sheet3").Name = "VIC Report"
    
    Rows("1:1").Copy
    Sheets("VIC Report").Range("A1").PasteSpecial

    'FIltering VIC Locos
    Cells.Find(What:="G532", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlToRight).Offset(0, 9)).Copy
    Sheets("VIC Report").Select
    Cells(Rows.Count, "A").End(xlDown).PasteSpecial
    
End Sub

your help is greatly appreciated
thanks
Sam
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I'd use End(xlUp) as it's less error prone, then add Offset(1) to move to the first empty row. Without the Offset your code is pasting to the last populated row.

Sheets("VIC Report").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial

But copying data to other sheets is really inefficient; just use Pivot Tables. Then there's no need to keep copying as new data is added, you just refresh.

HTH,
 
Upvote 0
thanks Smitty
im also trying to search for unique data in column A (eg G532, VL362, S313) and copy a few columns. the issue i have is that unquie data might not always be there.
my code is
Code:
    lr = Cells(Rows.Count, "A").End(xlUp).Row
        For r = 1 To lr
    If Cells(r, lr).Value = G515 Then
    Range(r, 0).End(xlToRight).Offset(0, 9).Copy
    Sheets("VIC Report").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
        Else
    If Cells(r, lr).Value = VL362 Then
    Range(r, 0).End(xlToRight).Offset(0, 9).Copy
    Sheets("VIC Report").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
    
    End If
    End If
    Next r

appreciate your help
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,199
Members
450,469
Latest member
brent3162

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