VBA - Excel Filters - Copy Filtered Rows

Post date: Jul 19, 2011 3:42:38 PM

The followingExcel AutoFilter VBA code copies the filtered rows from the active sheet to Sheet2.

Sub CopyFilter() 'by Tom Ogilvy Dim rng As Range Dim rng2 As Range With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else Worksheets("Sheet2").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Sheet2").Range("A1") End If ActiveSheet.ShowAllData End Sub