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