Find Rows and Copy to new worksheet

Dummy Excel

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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

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
1,003
Office Version
  1. 2019
  2. 2010
  3. 2007
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,951
Messages
5,834,535
Members
430,295
Latest member
amdis

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