Pivot tables share source data "after the fact"

[Don S]

When you build pivot tables, you are given the option to share source
data with an existingr pivot table (when applicable).

If this option was not selected when the workbook was developed, is
there a way to get pivot tables using the same data table to share the
cache - short of recreating them?

Thanks!
Don S

 

[Gary Brown]

Great Question!!!
The only way I can think to do this is to know what 'Cache Index' each pivot
table has. The CacheIndex is an identifier of the range of data used for
that pivot table.

To find out what cacheindex each of your pivot tables has, run the macro
below called "Pivot_Table_Information".

Once you've identified which pivot table has which Cache Index, run the
macro below called "Change_My_CacheIndex".
REMEMBER TO CHANGE THE EXAMPLE FOR THE ACTUAL WORKSHEET NAME AND PIVOT TABLE
NAME!!!

HTH,
Gary Brown

'/=====================================/
Sub Pivot_Table_Information()
Dim iRow As Long
Dim PivTbl As PivotTable
Dim wksht As Worksheet
On Error Resume Next

Worksheets.Add.Move _
After:=Worksheets(Worksheets.Count)

ActiveSheet.Range("A1").Value = "Worksheet"
ActiveSheet.Range("B1").Value = _
"Pivot Table Name"
ActiveSheet.Range("C1").Value = "Source Data"
ActiveSheet.Range("D1").Value = "Cache Index"

Range("A1").Select

'Go through one Worksheet at a time
For Each wksht In Worksheets
For Each PivTbl In wksht.PivotTables
iRow = iRow + 1
ActiveCell.Offset(iRow, 0).Value = wksht.Name
ActiveCell.Offset(iRow, 1).Value = PivTbl.Name
ActiveCell.Offset(iRow, 2).Value = _
PivTbl.SourceData
ActiveCell.Offset(iRow, 3).Value = _
PivTbl.CacheIndex
Next PivTbl
Next wksht

Cells.EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True

End Sub
'/=====================================/
Sub Change_My_CacheIndex()
Worksheets("Sheet1"). _
PivotTables("MyPivotTable").CacheIndex = 4
End Sub
'/=====================================/

 

[Don S]

Fantastic!!!!

Thanks, Gary.

It works great. It cut the time it takes to open and save the
workbook in half.

A side benefit I didn't expect was a tidy listing of the pivot tables
in my workbook and the source data tables they use. WOW!

Don S.

 

[Debra Dalgleish]

Another option is to change all pivot caches to match that of a
specified pivot table. There's sample code here:

http://www.contextures.com/xlPivot11.html

 

[Don S]

Thanks Debra,

That would work well when there is only one data source in the
workbook. I have to selectively change each pivot table cache since
my 30+ pivot tables use one of 8 different data tables in the same
workbook.

I can use this method in other workbooks, though. I built several "on
the fly" and am now trying to clean them up.

Thank you,

Don S