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.


Download Workbook

You can download the free practice Excel workbook from here.


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

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.

Dataset of excel vba advanced filter copy to another sheet

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.

Excel VBA code to copy to another sheet with advanced filter

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

result of excel vba code to advanced filter copy to another sheet

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.

Read More: How to Use Advanced Filter to Copy Data to Another Sheet in Excel


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.

excel vba advanced filter copy to another sheet by user selection

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

selecting range to filter for excel vba advanced filter copy to another sheet

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

selecting criteria for excel vba advanced filter copy to another sheet

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

selecting destination for excel vba advanced filter copy to another sheet

To see the result look at the image below.

result of excel vba advanced filter copy to another sheet by selection

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.

Related Content: Excel Advanced Filter Not Working (2 Reasons & Solutions)


Similar Readings


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.

running macro recording for excel vba advanced filter copy to another sheet

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

defining macro name for excel vba advanced filter copy to another sheet

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

selecting range, criteria and destination for excel vba advanced filter copy to another sheet

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.

result of excel vba advanced filter copy to another sheet by 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).

code of recorded macro for excel vba advanced filter copy to another sheet

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

modified code for excel vba advanced filter copy to another sheet

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

result of the modified code for excel vba advanced filter copy to another sheet

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

Read More: How to Use Advanced Filter for Unique Records Only in Excel


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo