March 5, 2016

Copy auto filtered data to another worksheet automatically with VBA

Posted on by Dinesh Kumar Takyar

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:

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. Pingback: Create Reports from Raw Data with Loops Auto Filter VBA | Excel VBA Training Videos

  4. 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.
    Example
    “=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:
    “{=INDEX(Sheet1!A:A,MATCH(B1,Sheet1!B:B,0))}”

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

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

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