I have a pivot table with two report page filters at the top. One is a string-based filter (4 options of universes to select from) and one is date-based (one entry per month, so 09/01/2018 and 08/01/2018 going back 5 years). I’m trying to use VBA to update the date filter to select, say, the most recent 3 months.
I’ve tried everything. The .PivotItems approaches don’t work for the report filter with dates. It works perfectly if I try it with the universe filter right above it but says it can’t access the PivotItems method of the PivotFields class when I switch the code to the other filter, so it must be due to the fact they’re dates.
I can clear all filters on my date filter just fine, and I can use .CurrentPage to set the filter to a single value - say, 09/01/2018 - but I can’t use these methods to deselect everything and select a few specific months.
I used the macro recorder as a last resort and it saves it using the .PivotItems method to change the filter, but if I run that exact macro it saved it throws the same error that it can’t acces PivotItems.
I’m at wits end. Does the fact the filter uses dates change the way I have to acces it with VBA? And how on earth do I do it?
Thanks!