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
Dim rng As Range
Dim autofiltrng As Range
With ActiveSheet
.Range(“A1″).AutoFilter field:=2, Criteria1:=”>=30000″
End With
.Range(“A1″).AutoFilter field:=2, Criteria1:=”>=30000″
End With
With ActiveSheet.AutoFilter.Range
On Error Resume Next
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!”
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
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: