Set Multiple Pivot Cache to read one cache

Mike Blackman

Well-known Member
Jun 8, 2007
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;

Sheets(1).PivotTables("PivotTable1").PivotCache = Sheets(1).PivotTables("PivotTable1").PivotCache

Can anybody shed some light for me?

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Thank you Debra Dalgleish (Again),

For peoples reference to this see here

Sub ChangePivotCache()
'change pivot cache for all pivot tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet

  For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex
    Next pt
  Next wks

End Sub
Upvote 0
you would have needed to use Set (if it worked) but you appear to be reassigning the same cache? You ought really to have used one pivot table as the basis for all the others instead of assigning the DNR to them of course...
Last edited:
Upvote 0
Hi Rory,

It does appear to have worked ok, and the file is now seeing the fruits of my labour from the size perspective. Was 26Mb, then scarilly upto 68Mb, now at a happy 8Mb.

I am however concerned due to your post, Can you explain a bit more for me Rory, I'm not getting what your telling me?
Upvote 0
Don't panic, that is essentially what you have done now. I got the impression somehow that you had set up the DNR after creating the pivot tables and had then gone back and reset each table. I was just saying that you could have simply based each one off one specific table, rather than off a range.
Upvote 0
Hi Rory,

The table were already in place reading a static range, I put the data set they were all reading in a DNR and went into the wizard of each table and set the Source to read the DNR then had the issues.

I guess there is a chance that the guy that put the report together didn't link them all to one in the first place.
Upvote 0
Oh OK - you could have just gone one step further back in the wizard and pointed each one at another pivot table then.
Upvote 0

This code worked for me in one workbook, but not another. I put this code in as a macro in the workbook in question. I then thought I found out that the trick was to replace the sheets ("Pivot") name with a valid sheet name from my workbook (any one that had a pivot table on it). Is that what you are supposed to do? If not, can you tell me do I put this in as an Excel macro and what sheet name should I reference in this code?

I think this code came from Accenture, and I downloaded their sample file, etc, but don't see the macro code in that sample file.

Appreciate your help if you are still posting, or if not, maybe someone else would know?

Thanks again !

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back