VBA to Copy Data to Another Sheet with Advanced Filter in Excel: 3 Methods

Method 1 – Embed VBA Code to Copy Data to Another Sheet with Advanced Filter in Excel

Steps:

Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

In the pop-up code window, from the menu bar, click Insert -> Module.

Copy the following code and paste it into the code window.

Sub AdvancedFilterCode()
Dim iRange As Range
Dim iCriteria As Range
'set the range to filter and the criteria range
Set iRange = Sheets("Original").Range("B4:E12")
Set iCriteria = Sheets("Original").Range("G4:H5")
'copy the filtered data to the destination
iRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=iCriteria, CopyToRange:=Sheets("Target").Range("B4:E4"), Unique:=True
End Sub

Your code is now ready to run.

Press F5 on your keyboard, or select Run -> Run Sub/UserForm from the menu bar. You can also just click on the small Run icon in the sub-menu bar to run the macro.

After the code execution, look at the image below for the result.

Only the data where John’s Marks are less than 80 is copied in the Target sheet from the Original sheet with the Advanced Filter of VBA.

Method 2 – Implement VBA Macro to Filter Data by User-Defined Selection

Steps:

Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
In the code window, copy the following code and paste it.

Sub AdvancedFilterBySelection()
Dim iTrgt As String
Dim iRange As Range
Dim iCriteria As Range
Dim iDestination As Range
On Error Resume Next
iTrgt = ActiveWindow.RangeSelection.Address
Set iRange = Application.InputBox("Select Range to Filter", "Excel", iTrgt, , , , , 8)
If iRange Is Nothing Then Exit Sub
Set iCriteria = Application.InputBox("Select Criteria Range", "Excel", "", , , , , 8)
If iCriteria Is Nothing Then Exit Sub
Set iDestination = Application.InputBox("Select Destination Range", "Excel", "", , , , , 8)
If iDestination Is Nothing Then Exit Sub
iRange.AdvancedFilter xlFilterCopy, iCriteria, iDestination, False
iDestination.Worksheet.Activate
iDestination.Worksheet.Columns.AutoFit
End Sub

Your code is now ready to run.

Run the macro.
A pop-up box will appear. Select the range that you want to filter (in our case, it is from range B4 to E12).
Press OK.

Another pop-up box will appear. This time, you must select the criteria range stored in your dataset (for our dataset, the criteria range is from Cell G4 to H5).
Press OK.

Another pop-up box will appear. Select the destination range where you want to store the copied data. In our case, it is Cell B2 in the Destination sheet.
Press OK.

To see the result look at the image below.

The data where John’s Marks are less than 80 is copied in the Destination sheet from the Original sheet with the Advanced Filter of VBA.

Method 3 – Apply VBA Macro to Copy Data to Another Sheet by Macro Recording in Excel

Steps:

Open a new worksheet (in our case, it is sheet Filtered).
Store only the header row of the original dataset.

Go to the Original sheet. At the bottom left side of the sheet, you will see a small macro sign. Click the sign to start recording a macro.

A Record Macro pop-up window will appear. Provide a Macro name that you want. We define AdvancedFilter as our Macro name.
Select where you want to store the macro. We wanted to store the macro in the existing workbook, we selected This Workbook.
Click OK.

Go back to the Original sheet and you will notice that the macro you just initiated started recording.

You have to go to the sheet that will hold the copied data (e.g. Filtered sheet).
Have an active cell in that sheet and go to Data -> Advanced.

An Advanced Filter pop-up box will appear.
Check the Copy to another location option from the Action
In the text box beside the List range, go to the Original sheet and select the range to filter (for our dataset, the range is B4:E12).

In the text box beside the Criteria range, select the criteria range (John’s Marks is less than 80) stored in the Original sheet (for our dataset, the range is G4:H5).

In the text box beside the Copy to, go to the Filtered sheet, sheet where you want to store the copied data and select the header range (for our dataset, the range is B4:E4).
Click OK.

Look at the image below to see the result produced by this whole procedure. Only the data where John’s Marks are less than 80 is copied to the Filtered sheet from the Original sheet with the macro recording.

Click the macro sign at the bottom left of the sheet to stop the macro recording. You now have a recorded macro that will perform the above process described every time you run it.

If you add new data to the Original sheet, the Filtered sheet won’t be updated even if the data fulfil the criteria.

Our newly Filtered sheet is to be automatically updated by executing the code when we add new data to the Original sheet. We need to modify the code a little bit.

All the steps to get what we want are shown below.

Steps:

From the tab, select View -> Macros -> View Macros.

A Macro pop-up window will appear. Select the Macro name that you just created by recording (AdvancedFilter for our case).
Click Edit.

The code behind the recorded macro will appear in the code window (see the image below).

Delete the blue marked part (shown in the picture below) from the code.

Modify the code just as shown in the following picture.

The updated code will be:

Sub AdvancedFilter()
Sheets("Original").Range("B4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Original").Range("G4:H5"), CopyToRange:=Sheets("Filtered").Range("B4:E4"), Unique:=False
End Sub

Save this code.
Go back to the Original sheet and add new data which will fall under the criteria. For instance, we added another row of John’s information where the obtained Marks is 76 which falls under the criteria of Marks less than 80.