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.
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:
Thanks for the help.
Please Login or Register to view this content.