Copy filtered data without headers to another worksheet

AndyB63

New Member
Joined
Feb 27, 2015
Messages
23
Can somebody please help me with VBA code to copy filtered data (excluding headers) to another sheet within the same spreadsheet.

I'm using a data filter which begins at cell A10 in Sheet X and I'd like to copy the filtered data excluding the header row and the first row (which is merely a sub-header within the data range) to the next available row in a range in Sheet Y.

Not being an expert on VBA, I have set up a range in Sheet Y called ENDPOINT (which is directly below the current last row with data) so that the macro could include an instruction which sends it to ENDPOINT, goes "End, Up", then down one row to locate the cell to which the data should be copied.

I'm aware that VBA will have a much more efficient way of locating the cell where I want the data copied. Like I said, I'm not an expert!

Any help gratefully received.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
To filter for the number 1 in column A in sheet X, with the range being from A10 to column E and last row (headers in row 10) and copying to the next row in sheet Y (based on column A) would be something like...

Rich (BB code):
Sub Filterit()
    With Sheets("X").Range("A10:E" & Sheets("X").Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=1, Criteria1:="1"
        
        On Error Resume Next
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy _
                Sheets("Y").Range("A" & Rows.Count).End(xlUp).Offset(1)
        On Error GoTo 0
        .AutoFilter
    
    End With
End Sub
 
Upvote 0
Mark,

Many thanks for the reply. As you've realised by now, I'm still finding my way around VBA. I tried, unsuccessfully, to adapt your solution to my spreadsheet but I received a "Subscript out of range" error as I assume my adaption of your code is incorrect.

To be as specific as possible, the settings for the Advanced Filter in Sheet X are:

List range....$A$10:$R$657
Criteria........$D$7 :$D$8

and the cells to which I'd like to copy the filtered data (excluding the header and the first row of data) in Sheet Y is the first empty row below E11:V11.

I hope that makes sense.
 
Upvote 0
One more thing re the above........I said that I wished to copy the filtered data to Sheet Y; I should've said I wish to Edit/Paste Special/Values the filtered data.
 
Upvote 0
Hi AndyB63, afraid I am not home now to post any code until tonight but it isn't surprising that you are struggling as the syntax for advanced filter is slightly different to autofilter (you didn't mention it was advanced filter).
Hopefully someone will get back to you before I can.
 
Upvote 0
In the code below I haven't used the "the first empty row below E11:V11" in sheet Y as looking for the next entirely empty row after a range of columns is an inefficient way of defining a last row and so hopefully you will state another way of defining it.

For the moment it uses the first empty cell after the last used cell in column E.

Code:
[color=darkblue]Sub[/color] filtme2()
    [color=darkblue]Dim[/color] Lstrw [color=darkblue]As[/color] [color=darkblue]Long[/color]

    Application.ScreenUpdating = [color=darkblue]False[/color]

    [color=darkblue]With[/color] Sheets("X").Range("A10:R357")
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Sheets("X").Range("D7:D8")

        Lstrw = Sheets("Y").Range("E" & Rows.Count).End(xlUp).Row
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
        [color=darkblue]With[/color] .Offset(2).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            Sheets("Y").Range("E" & Lstrw).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
            [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0

        [color=darkblue]End[/color] [color=darkblue]With[/color]
        Sheets("X").ShowAllData
    [color=darkblue]End[/color] [color=darkblue]With[/color]

    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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