Find Rows and Copy to new worksheet

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
994
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 

Dummy Excel

Well-known Member
Joined
Sep 21, 2005
Messages
994
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,956
Members
414,351
Latest member
james27

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
Top