PivotTable auto-refresh with external data

[Pilou-bcn]

Hello;
I am have trouble finding a solution about auto-refresh.
I have an XL file with all the data.
I created another file where my Pivots tables are. I have the auto-refresh
at opening selected.
In the Pivot workbook, I have a main pivot table and charts. Then I created
other Pivot charts that use the information from the initial one.
When I change the file source, only the main pivot table/chart get the data
refresh. All the other still display the data from the previous data sheet.
Could you please give me a hand on this please?
Thanks in advance.

 

[Debra Dalgleish]

If you have different pivot charts, you must have also created different
pivot tables. Somehow, those have created one or more additional pivot
caches, and don't use the original pivot cache.

There's sample code here that will change all the pivot tables to use
the same pivot cache.

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

 

[Pilou-bcn]

Hello Debra;
Thanks for your answer.
Could you please tell me where I have to copy and paste this code?
Do I have to paste it in each sheet where the pt are?
Is there any variables that need to be change?
The names of my tables are the following:
All Desks Table (Main PT)
PUN Table
CHS Table
US Table...
Could you please put an example on how the code should look like?
Do I need to add this code in the "View Code" of the sheet?
Sorry to ask all these questions but I am not so familiar with this.
Thanks again.

 

[Debra Dalgleish]

Copy the code from the Change the Pivot Cache section on the pivot cache
page:

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

Then, follow the instructions here:

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

to copy the code to a regular mode, and run the code.

The only thing you should need to change in the code is the sheet name,
where your main pivot table is stored.

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

 

[Pilou-bcn]

Thanks a lot Debra, everything is fine now. We had to add another bit of
code to have the script running when the workbook is open.
Thanks again, you've been a great help.