LinkBack URL
About LinkBacks
Afternoon all,
I'm creating a report and I'd like the month filter in the pivot table to dynamically change based on the selection in a drop down list (example attached).
Thanks in advance.
Snook
Afternoon all,
I'm creating a report and I'd like the month filter in the pivot table to dynamically change based on the selection in a drop down list (example attached).
Thanks in advance.
Snook
The secret to changing a filter multiselect is that first you have to turn everything "on" and then turn off what you don't want.
Please Login or Register to view this content.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Hi dflak,
Thanks for this, much appreciated. It's functioning as I would like but it doesn't appear to be selecting the right months.
Any suggestions?
Snook
The reason it fails is because Pivot Filters treat numbers and dates as strings..
Here is the modified code:
Please Login or Register to view this content.
Magic, cheers dflak. I did try to figure it out myself but what you wrote seemed logical to me so I couldn't see what the problem was?!?!
Me again,
I've been trying to use the solution in my workbook but I'm struggling to apply it to multiple pivot tables.
I assumed I'd be able to replicate the macro 3 times for each pivot table but that doesn't appear to work.
I've amended my workbook to better reflect the data I'm working with.
I'd be grateful if someone could tweak the solution provided above so that it will work with multiple pivot tables.
Thanks in advance,
Snook
There is a slightly different plan of attack. This code loops through all the sheets, finds whatever pivot tables are on the sheet and resets the filter. If you add pivot tables that don' use Month No, then this code will break.
Cheers dflak, much appreciated!
Thankfully (at the mo) all the pivot tables use the MonthNo field.
Thanks again,
Snook
If you add other pivot tables, then the code would have to be modified. I would recommend telling the spreadsheet what sheets / tables need updating in a table that can be on a hidden sheet. You can loop through the contents of this table to get the sheets and pivot tables by name. The core code - turn everything on and then turn off what you don't need - will remain the same.
Thanks for the advice dflak, much appreciated!
Snook
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks