VBA to Copy Data to Another Sheet with Advanced Filter in Excel

When we are dealing with multiple Excel sheets, sometimes we have to copy data from one spreadsheet to another with certain conditions for better readability. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to copy data to another sheet with the advanced filter in Excel with the VBA macro.

Copy Data to Another Sheet with Advanced Filer in Excel VBA: 3 Ways

Look at the following dataset. There is an Excel worksheet named Original. This sheet consists of some data from range B4 to E12. That range also consists of duplicate values. Range G4 to H5 holds the criteria. What we want to do is, we will copy the data from range B5:E12, when John from the Name column has Marks less than 80 (criteria in Cell G4:H5) and paste those specific data to other sheets with the Advanced Filter in Excel with three different methods.

We will see how to insert hardcoded macro to copy data, how to advance filter by selection and how to transfer data from one sheet to another by recording the macro. And the above dataset will be the example to execute all the methods mentioned.

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

Here, you will learn the VBA code to copy the only data from the Original sheet when John’s Marks is less than 80 to another sheet named Target with the advanced filter.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• Next, in the pop-up code window, from the menu bar, click Insert -> Module.

• Then, 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.

• Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. 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 to see the result.

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

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

Now, you will learn how to filter the only data from the Original sheet when John’s Marks is less than 80 to another sheet named Destination from a user-selected range with the advanced filter of VBA in Excel.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, 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.

• Now, Run the macro.
• Then, a pop-up box will appear. Select the range that you want to filter (in our case, it is from range B4 to E12).
• Later, press OK.

• Next, another pop-up box will appear. This time you have to select the criteria range that you have stored in your dataset (the criteria range from Cell G4 to H5 for our dataset).
• Again, press OK.

• For the last time, another pop-up box will appear. And now, you have to select the destination range where you want to store the copied data. In our case, it is Cell B2 in the Destination sheet.
• Then, press OK.

To see the result look at the image below.

Finally, only 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.

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

In this section, you will get to know the solution to the same problem of extracting only data from the Original sheet when John’s Marks is less than 80 to another sheet named Filtered by Macro recording of VBA in Excel.

Steps:

• First of all, open a new worksheet (in our case, it is sheet Filtered).
• In that sheet, store only the header row of the original dataset.

• Next, go to the Original sheet. You will see a small macro sign at the bottom left side of the sheet. Click the sign to start recording a macro.

• Then, a Record Macro pop-up window will appear. Provide a Macro name that you want. We define AdvancedFilter as our Macro name.
• Next, select where you want to store the macro. We wanted to store the macro in the existing workbook, so we selected This Workbook.
• Later, click OK.

• Now, go back to the Original sheet and you will notice that the macro you just initiated started recording.

• Then, 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.

• Next, an Advanced Filter pop-up box will appear.
• First, check the Copy to another location option from the Action
• Now, 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).

• Then, 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).

• Now, 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).
• Lastly, click OK.

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

• Now, 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.

But there is a drawback of this method. If you add new data to the Original sheet, the Filtered sheet won’t be updated even if the data fulfil the criteria.

We want our newly Filtered sheet to be automatically updated by executing the code when we add new data to the Original sheet. To do that, we need to modify the code a little bit.

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

Steps:

• At first, from the tab, select View -> Macros -> View Macros.

• Later, a Macro pop-up window will appear. Select the Macro name that you just created by recording (AdvancedFilter for our case).
• Then, click Edit.

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

• Now, delete the blue marked part (shown in the picture below) from the code.

• Then, modify the code just as shown in the following picture.

• Now, 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.
• Now, 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.

• Then, Run the code and look at the following image for the result.

• There is a newly copied row in the Filtered sheet of John’s information with Marks 76 that fulfils the criteria (Marks<80).

Download Practice Workbook

You can download the free practice Excel workbook from here.

Conclusion

To conclude, this article showed you 3 different criteria on how to copy data to another sheet with the advanced filter in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Related Articles

• Excel VBA Examples with Advanced Filter Criteria
• Excel VBA: Advanced Filter with Multiple Criteria in a Range