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:

3 thoughts on “Copy auto filtered data to another worksheet automatically with VBA

  1. Vutukuri Bharat

    Hi Sir,

    I have the data of range A3 to A50 but i need to trim the data of first 4 letters and i need to keep the data in particular row A8,A15 and A25 etc… can u send the code for that paricular Colums data cant be trim.

    Reply
  2. john watkins

    i love watching your videos, but i am having trouble. I have several columns with data on sheet 1. But i have one column that i will be filtering the data with. If multiple cells in this column has an ” x ” then i filter the data with those cells. Then i want to copy certain columns of data, that contain the ” x’s ” and paste it in sheet 2. None of the data will change just the column with the ” x’s “.

    Reply
  3. Pingback: Create Reports from Raw Data with Loops Auto Filter VBA | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *