Macro Select visible cells after auto filter - except header

[bbran19]

Is there any way to select visible cells after auto-filter EXCEPT the header row? I need to copy and paste into a new worksheet but I don't want to copy the header row.

 

[JackDanIce]

See if this helps:

   With Sheets("Sheet1")
        With .Cells(1, 1).Resize(10, 10)
            .AutoFilter Field:=1, Criteria1:="<>"
            .Offset(1).Resize(9).Specialcells(xlcelltypevisible).Copy
        End With
    End With

The blue 10 is for the number of rows in the area being filtered, including header row
Offset(1) goes to the 'next' row but we reduce the count of rows in the area by 1 since excluding header row (i.e. 10 - 1 = 9)
Then copy the visible cells for that area only

 

[My Aswer Is This]

Assuming you want to filter a Sheet named "Sheet1" and copy those rows into a Sheet named "Sheet2"
And you want to filter the values in Column "A" which have the value "Pending"

Use this script.

Modify the sheet names marked in Red if your sheet names are not as mentioned above.
And modify the filter value and column to search as needed.

Sub Filter_Me()
'Modified 10-6-17 1:00 PM EDT
Application.ScreenUpdating = False
Dim Lastcolumn As Long
Dim CopySheet As String
Dim PastSheet As String
CopySheet = [COLOR=#ff0000]"Sheet1[/COLOR]"
PasteSheet = "[COLOR=#ff0000]Sheet2[/COLOR]"
Lastcolumn = Sheets(CopySheet).Cells(1, Columns.Count).End(xlToLeft).Column
Lastrow = Sheets(PasteSheet).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    
    With Sheets(CopySheet).Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Lastcolumn))
        .AutoFilter Field:=[COLOR=#ff0000]1[/COLOR], Criteria1:="[COLOR=#ff0000]Pending[/COLOR]"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets(PasteSheet).Cells(Lastrow, 1)
    End With
Sheets(CopySheet).AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[scapaflow]

Assuming you want to filter a Sheet named "Sheet1" and copy those rows into a Sheet named "Sheet2"
And you want to filter the values in Column "A" which have the value "Pending"

Use this script.

Modify the sheet names marked in Red if your sheet names are not as mentioned above.
And modify the filter value and column to search as needed.

Sub Filter_Me()
'Modified 10-6-17 1:00 PM EDT
Application.ScreenUpdating = False
Dim Lastcolumn As Long
Dim CopySheet As String
Dim PastSheet As String
CopySheet = [COLOR=#ff0000]"Sheet1[/COLOR]"
PasteSheet = "[COLOR=#ff0000]Sheet2[/COLOR]"
Lastcolumn = Sheets(CopySheet).Cells(1, Columns.Count).End(xlToLeft).Column
Lastrow = Sheets(PasteSheet).Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    
    With Sheets(CopySheet).Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Lastcolumn))
        .AutoFilter Field:=[COLOR=#ff0000]1[/COLOR], Criteria1:="[COLOR=#ff0000]Pending[/COLOR]"
        .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy Sheets(PasteSheet).Cells(Lastrow, 1)
    End With
Sheets(CopySheet).AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

This is awesome!!! I didn't see a thank you on here, but thank you for posting this solution!!!

I received a run-time error 1004 "Application-defined or object-defined error" on this line:

With Sheets(CopySheet).Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Lastcolumn))

And I am trying to use your Filter / Copy / Paste with more than 1 AutoFilter criteria and my 2nd criteria is actually a defined variable.

With Sheets(CopySheet).Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Lastcolumn))
.AutoFilter Field:=1, Criteria1:="Pending"
.AutoFilter Field:=2, Criteria1:=DateToday

Can you help with the error and maybe post an example for multiple criteria?

 

[My Aswer Is This]

This is not a proper way:

.AutoFilter Field:=2, Criteria1:=DateToday

If should be:

.AutoFilter Field:=2, Criteria1:=Date

But not sure that is only problem.

I think you should start a new posting and explain in detail what you need.

​

_{^{<strike>
</strike>}}​