How select same value in multiple pivot tables?

[careware]

Suppose i have 10 pivot table reports.


every month i need to select country field (for example) via drop down menu of pivot tables to generate different reports.


is there any way through which i select the country from single drop down list and all the 10 pivot tables automatically select that country in one go?


thanks in advance

 

[Hui]

Careware


There is no built in way in Pivot Tables to do what you want


You could easily setup a macro to get a value from a Cell and then use that for all the Pivot Tables.


something like the following

=============


Sub Pivot_Field()


PLookup = Range("A1").Value


ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").CurrentPage = PLookup


ActiveSheet.PivotTables("PivotTable2").PivotFields("Year").ClearAllFilters

ActiveSheet.PivotTables("PivotTable2").PivotFields("Year").CurrentPage = PLookup


'Etc

End Sub


=============

Change the field name from Year to suit.

 

[Hui]

Careware


Two more ideas:


1. You could also, pre-select your data.


Instead of having all your Pivot tables, based on your data directly,

have them based on a Temporary Data Area, which already has the selection criteria applied.

Change the selection criteria and update the pivot tables, It would require use of named dynamic named areas and could easily be automated to update pivot tables


2. Base the pivot tables on a Table of Data and Use Table Selection criteria to pre-select the data

 

[careware]

Thanks a lot Hui for your kind help