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:
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.
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 “.
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))}”
Didnt work well. it prompting the message box and not copied in the sheet2. The value i set was correct
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.
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
split worksheet into different worksheets based on row content