Mike Blackman
Well-known Member
- Joined
- Jun 8, 2007
- Messages
- 2,494
Hi All,
I have a workbook I have been making more efficient, within the workbook there are about twenty Pivot Tables all reading the same data set. I have created a Non-Volatile DNR for this range and have obviously set all the Pivot tables to read this NR. Problem I am now having is that the file size has increased by 300% and I am assuming this is because all of the Pivot Tables are all acting like they have they're own cache instead of all reading from the one, if indeed that is how it works.
I assume this is the problem anyhow, I have tested by running all of the sheets into seperate files and saving them down and indeed the additional file size is being generated only by the sheets that hold pivot tables.
I was thinking of looping through each table and using something like this but it soesn't work;
Can anybody shed some light for me?
I have a workbook I have been making more efficient, within the workbook there are about twenty Pivot Tables all reading the same data set. I have created a Non-Volatile DNR for this range and have obviously set all the Pivot tables to read this NR. Problem I am now having is that the file size has increased by 300% and I am assuming this is because all of the Pivot Tables are all acting like they have they're own cache instead of all reading from the one, if indeed that is how it works.
I assume this is the problem anyhow, I have tested by running all of the sheets into seperate files and saving them down and indeed the additional file size is being generated only by the sheets that hold pivot tables.
I was thinking of looping through each table and using something like this but it soesn't work;
Code:
Sheets(1).PivotTables("PivotTable1").PivotCache = Sheets(1).PivotTables("PivotTable1").PivotCache
Can anybody shed some light for me?