Let’s say I have a spreadsheet with multiple parts. In one part is a drop-down list; in another part is a pivot table, with a filter. I want the selection on my drop-down list to do multiple things; one of which is to change the value on the pivot table filter (to the corresponding value chosen from the drop-down list).
Is that possible?
Yes, it is possible. I do this on several reports that I build in Excel.
When you say “macro” I assume you know how to code in VBA, and not just use the macro recorder. However, you can use the macro recorder to discover some of the code you will need.
It may be a little more complex that you might initially think, though.
You have to have code to reset all the values in your filters as well as code to set the desired filters.
Here’s a code snippet from one of my reports:
For Each pvtItem In prmPivot.PivotFields(strColField).PivotItems
…
Then for the item you want to activate in the filter…
pvtItem.Visible = True
Here’s a link to an online reference for the Pivot Table object model:
Good luck.