It’s hard to believe that Excel Advanced Filter Week is drawing to a close. I hope that the three filter feeding shark species, and you, have enjoyed this tribute to Discovery Channel’s Shark Week.
Today, we’ll see how to record a macro while using the Advanced Filter, and edit that macro, so it will automatically adjust if the data changes.
Advanced Filter Results are Static
When you run an Advanced Filter, and copy data to a different location, the copied data is not linked to the original data. If the original data changes, the copied data won’t automatically update. You could run the Advanced Filter again, to update the copied data.
If you plan to update the original data frequently, you can save time and effort, by creating a macro to run the Advanced Filter.
Record the Advanced Filter Steps
In this example, we recorded a macro while filtering the top orders onto a different worksheet. The original data is on the Orders sheet, and the copied data is on the TopOrders sheet.
Here is the code that was recorded by the Excel macro recorder. I added a few line breaks to make it narrower.
Edit the Advanced Filter Macro
After you record the macro, you can edit the code, to make it flexible, and to remove any unnecessary lines of code.
In the recorded code, you can delete the following:
- comment lines, that start with an apostrophe.
- lines that end with Select
The list range in the code is set as “A1:D15”. You can change that to the CurrentRegion for cell A1, so the range will automatically adjust, if rows are added or removed.
The other change you should make is to add the sheet name for the CopyToRange. Then, if the destination sheet is not active when you run the macro, it will still work correctly.
The Edited Advanced Filter Macro
Here is the final version of the edited Advanced Filter macro.
Sub TopOrderFilter() Sheets("Orders").Range("A1").CurrentRegion.AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Orders").Range("F1:F2"), _ CopyToRange:=Sheets("TopOrders").Range("A1:D1") _ , Unique:=False End Sub
Note: In Excel 2007 and later, when you save the file, use the Binary, or Macro-Enabled File Type.
Download the Advanced Filter List Workbook
To see the sample data, and test the filter, you can download the Advanced Filter Macro sample workbook. The file is in Excel 2007 format, and is zipped. The file contains macros, so enable them if you want to test the code.
Watch the Advanced Filter Macro Video
To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial.
Or watch on YouTube: Excel Advanced Filter Macro