excel vba‎ > ‎

VBA - Excel Filters - Copy Filtered Rows

posted Jul 19, 2011, 8:42 AM by Roberto Felicini

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  
Comments