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:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

4 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.
    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))}”

Leave a Reply

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