Copy auto filtered data to another worksheet automatically with VBA

How to copy auto filtered data to another worksheet automatically with VBA. Watch the video:

 

Watch this video on YouTube.

The complete VBA code to copy auto filtered data to another worksheet:

Sub copyFilteredData()
Dim rng As Range
Dim autofiltrng As Range
With ActiveSheet
.Range(“A1″).AutoFilter field:=2, Criteria1:=”>=30000″
End With
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count – 1, 1).SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

End With

If autofiltrng Is Nothing Then
MsgBox “no data available for copying!”
Else
Worksheets(“Sheet2”).Cells.Clear
Sheet2.Range(“A1”) = “name”
Sheet2.Range(“B1”) = “salary”
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count – 1).Copy Destination:=Worksheets(“Sheet2”).Range(“A2”)
End If
ActiveSheet.ShowAllData
End Sub
Further reading: