Set Multiple Pivot Cache to read one cache

[Mike Blackman]

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?

 

[Mike Blackman]

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

 

[RoryA]

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...

 

[Mike Blackman]

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?

 

[RoryA]

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.

 

[Mike Blackman]

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.

 

[RoryA]

Oh OK - you could have just gone one step further back in the wizard and pointed each one at another pivot table then.

 

[Mike Blackman]

Oh well, All seems ok at the moment.....

Cheers Rory

 

[JOHNOQ]

Mikey,

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 !

John