make existing pivot charts point at the same database

[Valeria]

Dear experts,
I have an excel spreadsheet containing many pivot charts. They get data from
the same database, but (my fault when building the file) do not point to it
only once to reduce the doc size (Excel normally gives you the option to use
the same database as another pivot chart if equal to another one already
existing).

When I try to go back through the pivot table wizard to the area of the
data, I am not given the possibility of using an existing database. Is there
a way I can tell Excel to point only once to the same database for all pivot
charts without rebuilding them from scratch?

Many thanks,
Best regards,

 

[Debra Dalgleish]

There's sample code here that you can use to make all pivot tables use
the same pivot cache:

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

 

[Valeria]

Hi Debra,
thanks a lot, this has reduced BY HALF the size of my files!
Kind regards,
Valeria

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.