Macro to update pivot tables and use the SAME cache instead of repeatedly recreating cache

Hi all,

I have a bunch of reports that require dozens of pivot tables to be updated. The pivot tables within a single report all access the same data source, but across the portfolio of reports there are multiple different data sources (which is why I have the prompt to choose a source data file).

Some of the reports require the source data to be cached within the workbook so that people can manipulate the data. Other reports do not require the source data to be cached.

My problem is that when I need the source data cached, it looks like my macro is creating a cache for EACH pivot table even though I'm trying to only get it to create the cache once and have each subsequent pivot table reference that cache. The result is my file size is gigantic.

Could someone please take a look at my code and tell me what I'm doing wrong and how to fix it? I've highlighted the most relevant sections in orange.

Key goals:
Maintain ability to choose different file sources
Maintain ability to choose whether to turn on/off the Save Data with File
Create a single cache for each workbook and have all pivot tables source from that cache

Here is the macro:

Please Login or Register  to view this content.

Thanks for the help.

In case anyone is interested, I figured it out:

Please Login or Register  to view this content.

Fowzee1, welcome to the forum, and thanks for sharing your solution

For future reference, please use code tags when posting code (see my note below). I will add then for you - this time