Change Pivot Table Report Filters With VBA

With Report Filters at the top of your pivot table, you can select specific items from a field, and narrow the scope of your report.

In the pivot table shown below, the order date field has been grouped, and Quarters and Months are in the Report Filter area.

Change Report Filters Automatically

I’ve previously posted sample code for changing one (or all) pivot table’s report filters, based on changes in another pivot table. In one of my Contextures Blog posts, Ian asked if we could change one report filter in a pivot table, based on another report filter in the same pivot table.

That sounded like an interesting challenge, so I’ve created code that changes the Quarters filter, when a month is selected. For example, if you select May, it will change to Qtr2. If your fiscal year is different, you could change the quarters in the code.

If you select a different Quarter, the Months filter will automatically change to "(All)". Then, if you want a specific month within that quarter, you could select it.

Download the Sample File

To download the sample file, please visit the Pivot Tables VBA – Report Filters page on the Contextures website. The file is in Excel 2007/2010 format, zipped, and contains macros. Enable macros when you open the file, if you want to test the code.

In the file, to see the code, right-click on the pivot table sheet tab, and click View Code.

__________________