Control Multiple Pivot Table Filters by using a Combo Box in a different page

Originally Posted by bayzano

Hi Guys, this is my first post and I am pretty new to Excel VBA also, so I will appreciate your patience.

My issue is that I want to control multiple Pivot Table Filters by using one Combo box in a DIFFERENT Sheet. Why? Because I am building a Data Dashboard for an interesting amount of data that I want to report in a very eye-catching format for my superiors (that will be shown on one Excel Sheet); and use Pivot Table filters to show product performance in different periods of time. This Pivot Tables are going to be in a separate sheet.

I found some interesting solutions in Contextures.com (http://www.youtube.com/watch?v=3CGLi7t1lDE) for Changing All Pivot Tables When One Changes but when I record the Macro for changing data from Combo Box, something goes wrong.

I also found a way to control the Pivot Table Filter from a Combo Box http://www.datapigtechnologies.com/f...s/pivot10.html, but it didn't work for me because I use Excel 2007.

I hope you can help me with this issue.

Thanks a lot.

After a couple of shots I finally solved it. I used the this http://www.datapigtechnologies.com/f...s/pivot10.html, but added the code that was missing that calls the event from another sheet.

This is the macro that should be assigned to the Combo Box after following the instructions in the link above.

Please Login or Register  to view this content.

This works great for Excel 2007.

REPLACE: "PivotTableSheet" by the name of the sheet where your Pivot Tables are and "Filtered Value" with the filter of the Pivot Table you want to manipulate with the Combo Box.

Hope I was clear.

Hi there,

I am trying to achieve the exact same thing i.e. control multiple pivots with a combo box located in a different sheet.
Your modified code above doesn't reference the spreadsheet where the combo box is located.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Filtered Value").CurrentPage = Range("A1").Text

I'm assuming you have a combobox index in A1 which is in a different sheet from your pivots.
How does your code point to the correct worksheet where the combo box is? 'Range("A1").Text' references where the pivot table is (the active worksheet) not where the combo box is.

Hope my question is clear. Please answer as soon as possible.

Thanks.

Thank you