How to Create an Excel VBA Advanced Filter with a PivotTable Criteria Range
Question details
The user needs an Excel VBA macro to use a PivotTable as the criteria range for an Advanced Filter, copying the filtered results to a new location without overwriting existing data.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a master table using dynamic criteria defined in a PivotTable via VBA, and appending or inserting the results into a specific column destination.
- Observed behavior
- The Advanced Filter successfully copies the results to the specified range, but it does not automatically insert new rows and shift existing data down when the number of returned records is unknown.
Ensure that the Developer tab is enabled in your spreadsheet ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) before adding or running the VBA code.
Use VBA to Append Filtered Results to the Next Available Row
Set the Advanced Filter destination dynamically to the last empty row in the target column to append data safely and avoid overwriting existing records.
Because the built-in Advanced Filter does not automatically push existing rows down, the most efficient workaround is to identify the last used row in your target column and paste the new data directly beneath it.
Press ALT + F11 to open the Visual Basic for Applications (VBA) editor and insert a new Module.
Declare your variables for the ListObject (source table), PivotTable (criteria), and Range (destination).
Use Range("BL" & Rows.Count).End(xlUp).Offset(1) to instruct the macro to dynamically find the next empty row in column BL.
Call the AdvancedFilter method on your source table range using Action:=xlFilterCopy, CriteriaRange:=criteriaPivot.TableRange1, and CopyToRange:=destination.
Save your code and run the macro to filter the Master_ALL table and append the results seamlessly below the existing data.
Filter to a Temporary Sheet and Insert Rows Before Copying
Use this approach if you strictly need to insert new rows between existing data rather than appending to the bottom.
Run VBA and Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust support for VBA and Macros, allowing you to execute complex Advanced Filters, dynamic PivotTable criteria, and automation scripts exactly as you do in Microsoft Excel.
- 1. Download WPS Office: Install WPS Office for free from the official website.
- 2. Open your Macro Workbook: Open your existing .xlsm file containing the Advanced Filter macro in WPS Spreadsheet.
- 3. Enable Macros and Run: Navigate to the Developer tab, open the VBA Editor to verify your code, and execute your macro to filter your tables instantly.
Frequently Asked Questions
Can Advanced Filter in VBA automatically shift rows down?
No, the native Advanced Filter method (xlFilterCopy) overwrites existing cells in the destination range or clears the area below it. To shift rows down, you must write additional VBA code to count the filtered results and manually insert the rows beforehand.
How do I reference a PivotTable as a criteria range in VBA?
You can dynamically reference it using the TableRange1 property of the PivotTable object. In your code, define it as CriteriaRange:=ActiveSheet.PivotTables("YourPivotName").TableRange1.
Why is my VBA Advanced Filter overwriting my existing data?
When using CopyToRange, the Advanced Filter clears cells in the destination to ensure old data is removed before pasting. To prevent data loss when appending, set the destination dynamically to the next blank row using a formula like Range("A" & Rows.Count).End(xlUp).Offset(1).
