Copy filtered data without headers to another worksheet

[AndyB63]

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.

 

[MARK858]

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...

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

 

[AndyB63]

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.

 

[AndyB63]

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.

 

[MARK858]

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.

 

[MARK858]

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.

[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]