Pivot table

[zen_zen]

Hi

I have a spreadhseet which contains 5 pivot tables and a data source
each time i hvae to refresh each pivot table individually to update th
data, anyone know how to merge or make the 5 pivot table refer to a mai
pivot table, so that when i refresh 1 table, it will automaticall
refresh the other?


Also i not sure if i create 5 pivot tables separately, it make my fil
size become so big.

can anyone give me some idea

many thank

 

[Debra Dalgleish]

You can use a macro to change the pivot cache for each pivot table, so
they all use the same one. There is sample code here:

http://www.contextures.com/xlPivot11.html

 

[zen_zen]

Thank u for the suggestion. however, when i put in the code and run th
marco, it doesnt work and show debug on the the code.? Am I doing th
right way

 

[Debra Dalgleish]

What line is highlighted when you click the debug button and look at the
code?

In this line:

pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex

did you change "Pivot" to the name of a sheet in your workbook?