How to Loop Through PivotTable Filter Values in Excel VBA
Question details
The user needs an Excel VBA macro that can dynamically loop through changing PivotTable filter values and copy each filtered result to a new column on a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating a monthly reporting task where PivotTable filter options change dynamically, requiring a macro that doesn't rely on hard-coded values.
- Observed behavior
- The existing macro fails because it uses hard-coded filter values that do not exist in the new monthly dataset.
Ensure you have saved a backup copy of your workbook as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled to access the VBA editor.
Create a Unique List and Loop Through Items Using VBA
Extract unique filter values from your source data table into a temporary list, then iterate through that list to dynamically update the PivotTable filter and copy the results.
Relying on hard-coded values in your VBA macro will cause errors when the underlying data changes. The most reliable method is to instruct VBA to create a list of unique values currently available in the dataset, loop through each value to filter the PivotTable, and then clean up the temporary list.
In your VBA script, write code to copy the column containing the filter criteria from your source table to a temporary worksheet column, and use the RemoveDuplicates method to create a unique list.
Set up a 'For Each' loop to iterate through every cell in your newly created unique list range.
Within the loop, target your PivotTable field. Use the ClearAllFilters method first, then set the CurrentPage property of the PivotField to the current loop value.
Copy the updated PivotTable's data body range and header. Find the next available empty column on your destination worksheet and paste the values.
After the loop completes, delete the temporary unique list you created in the first step to keep the workbook clean.
Automate PivotTables and Macros with WPS Spreadsheet
WPS Spreadsheet offers excellent compatibility with Excel VBA macros and PivotTables, allowing you to run dynamic data loops and automate your monthly reporting effortlessly.
- 1. Open your macro workbook: Launch WPS Spreadsheet and open your existing .xlsm workbook containing the PivotTable and source data.
- 2. Access the Macro Editor: Navigate to the Developer tab on the top ribbon and click on 'WPS Macro' or 'Visual Basic' to view your code.
- 3. Run your loop: Execute your dynamic filter looping script directly in the WPS VBA editor to process the PivotTable updates perfectly.
Frequently Asked Questions
Why does my PivotTable macro fail when the monthly data changes?
If your VBA macro relies on hard-coded filter values (e.g., setting the filter explicitly to 'January'), it will produce an error when the source data changes to 'February' and 'January' is no longer an available option. You must dynamically read the available items instead.
How do I clear all filters from a PivotTable using VBA?
You can use the 'ClearAllFilters' method on your specific PivotField object. It is highly recommended to trigger this method before assigning a new 'CurrentPage' value during your VBA loop to avoid conflict errors.
Can I loop through the PivotItems collection directly instead of making a unique list?
Yes, you can loop through 'PivotTable.PivotFields("Field Name").PivotItems', but PivotTables often cache old data. Looping through the items directly might cause your macro to iterate over old, deleted items. Creating a fresh unique list from the source data ensures 100% accuracy.
