determining whether two pivots share a cache

[miconian]

I have a huge document consisting of many pivot tables. I inherited it, and the person who created it is long gone. Part of my job is to scale down this document.

I wonder if all the pivots are using the same cache, or not. If not, I could re-create them as copies of a single pivot, thus having only one cache. But I'm actually not sure if that's necessary. Is there a way to tell?

Note that I'm talking about cache, not source data. They are all using the same source data for sure.

This is in Excel 2007.

 

[jakeman]

I have a huge document consisting of many pivot tables. I inherited it, and the person who created it is long gone. Part of my job is to scale down this document.

I wonder if all the pivots are using the same cache, or not. If not, I could re-create them as copies of a single pivot, thus having only one cache. But I'm actually not sure if that's necessary. Is there a way to tell?

Note that I'm talking about cache, not source data. They are all using the same source data for sure.

This is in Excel 2007.

Try this macro to determine what cache is being used for each pivot table. Select a field inside of each and pivot table and then run this macro:

Sub ViewCacheIndex()

 On Error GoTo err_Handler 
 MsgBox "PivotCache: " & _     
    ActiveCell.PivotTable.CacheIndex      
Exit Sub 
 err_Handler: 
 MsgBox "Active cell is not in a pivot table"     
End Sub

Just thinking out loud but could some of the pivot tables be combined using grouping? I don't know how you slice your data but if the source data is all the same, I'm wondering if you could get by with using less pivot tables by combining more of the pertinent data in one pivot table...not that one would solve your problem but in terms of consolidating more of your data, using less that way.

 

[miconian]

Thanks jakeman, I will try your macro on Monday.

Yes, there could probably be fewer pivot tables as well. The document as it exists is huge and complex, so I'm going to be taking a number of different approaches to hone it down until it becomes more usable.