March 5, 2016

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!”
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
End Sub
Further reading:

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

  1. 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.

  2. 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 “.

  3. Dear Sir,
    My Name is Liazul Islam
    I am a great fan of you.
    Your VBA tutorial is very useful for everyone.
    Now I want to know how can I use excel Index match function in Array format.
    “=INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0))” This VBA is
    “= Application.WorksheetFunction.Index(Sheet1.Range(“A:A”), Application.WorksheetFunction.Match(Sheet2.Range(“B1”).Value, Sheet1.Range(“B:B”), 0))”
    But What is the form of the following function:

  4. Didnt work well. it prompting the message box and not copied in the sheet2. The value i set was correct

  5. sir
    i create d the 4 column tabe (A,B,C, & D) .I FILTR (A,B & C COLUMN) AND HOW TO COPY CELL VALUE FROM B COLUMN AFTER FILTR DATA.

  6. Respected sir,

    How to code using VBA to copy and paste only unique data from one sheet to another sheet in sorted order A to z

    Thanks in advance sir

Comments are closed.