Copy Filtered Data To Another Worksheet

Hi all

Sometimes you apply the autofilter to data and then want to copy the filtered data to another worksheet.

Below i will share the vba code to apply the filter and then copy the filtered data to another sheet

For Example

I have taken two worksheets "Main" & " Database"

Database:




Main:





Now suppose you select "infosys" in cell C1 of Main sheet and you want to filter the data on sheets("database") on the basis of value in cell C1 of main sheet and copy the filtered result to main sheet


Here is the code:

Method 1:-
Sub METHOD1()
Dim z As Long


Sheets("database").Activate
' below line will remove if any filter existing in database sheet
If Sheets("database").FilterMode Then
Sheets("database").ShowAllData
End If

Sheets("Main").Range("A3:z65356").Clear

z = Sheets("database").Range("a1").End(xlDown).Row
' Field is 3 because you are applying filter on third column
' criteria its picking from cell c1 from main sheet
Sheets("database").Range("$A$1:$c" & z).AutoFilter Field:=3, Criteria1:=Sheets("Main").Range("C1").Value

Sheets("database").AutoFilter.Range.Copy Sheets("Main").Range("A3")

If Sheets("database").FilterMode Then
Sheets("database").ShowAllData
End If
Sheets("Main").Activate

End Sub


Method 2:-

Sub METHOD2()
Dim z As Long


Sheets("database").Activate
If Sheets("database").FilterMode Then
Sheets("database").ShowAllData
End If

Sheets("Main").Range("A3:z65356").Clear

z = Sheets("database").Range("a1").End(xlDown).Row

Sheets("database").Range("$A$1:$c" & z).AutoFilter Field:=3, Criteria1:=Sheets("Main").Range("C1").Value

Sheets("database").Range("$A$1:$c" & z).SpecialCells(xlCellTypeVisible).Copy Sheets("Main").Range("A3")

If Sheets("database").FilterMode Then
Sheets("database").ShowAllData
End If
Sheets("Main").Activate

End Sub