Code to Change Multiple Pivot Table Filters

[David Schaleger]

Please help with code to change multiple instances of the same filter on several pivot tables on one worksheet. The worksheet has several PTs, and each PT has one filter which filters for the same value: a year/month in YYYY.MM (all numbers) format. I would like to be able to run the code, have it prompt for the filter value YYYY.MM (e.g. 2020.07 for July 2020), change all of the PT filters to that value, and then update the PTs. The field name for that value for each PT is "Yr.Mo". If updating results in a PT without any data (i.e. there is no data for that month), then the code should probably skip updating that PT (manually, Excel asks if I want to rename the old filter value to the new filter value); a pause in the action with a message stating that this PT (include PT name) has no data would be great! While it's not currently the case, it's possible that one or more PTs could have additional filters, or not have the "Yr.Mo" filter at all, so this possibility should be accounted for in the code. Excel version 2013.

 

[mole999]

just an idea, can you build pivots with code, thenyou could apply a variable to the code and just generate new pivots