On Pivot Caches

In Excel 2007, if you are trying to manipulate the Pivot Caches in multiple pivot tables, be careful.   The seemingly innocuous pivotcache.index property is a potential trap.  Note, that you can only change the pivotcache on EXTERNAL data by setting the CacheIndex.   ChangePivotCache is for internal use only (and doesn’t exist in 2003).
Take this code:
Dim masterIndex as Integer
masterIndex = masterPivotTable.PivotCache.Index
destinationPivotTable.PivotCache.Index = masterIndex
This sets the pivot cache of one pivot table to another.  It saves on memory and makes the pivot tables share the same data source.   Really handy for automating reporting.
However, there’s a trap here.   In Excel 2007, changing the pivot cache automatically removes the old pivot cache.   It works in 2003, and I suspect it keeps the old worksheets floating around.  This can create invalid indexes if you are not careful.
If you use the first worksheet as the master, that’s fine.  Its index will always be 1.  If you use a subsequent worksheet as a master, its index will change as you update subsequent reports with a lower index.
Pivot Cache Indexes Before Update:
1
2
[3]  Master Index
4
When you update 1, the indexes look like this:
Pivot Cache Indexes After Update:
1
[2] Master Index
3
Why?   Because the old index 1 got removed and the pivot caches got reindexed.
Fortunately, all of this is easily avoided.   Instead of tracking the index, track a pointer to the pivot table instead.
Dim MasterPivotTable as PivotTable
‘Insert your pivot table here
Set MasterPivotTable = Sheet1.PivotTable(1)
destinationPivotTable.PivotCache.Index = MasterPivotTable.PivotCache.Index

In Excel 2007, if you are trying to manipulate the Pivot Caches in multiple pivot tables, be careful.   The seemingly innocuous pivotcache.index property is a potential trap.  Note, that you can only change the pivotcache on EXTERNAL data by setting the CacheIndex.   ChangePivotCache is for internal use only (and doesn’t exist in 2003).

Take this code:

Dim masterIndex As Integer

masterIndex = masterPivotTable.PivotCache.Index

destinationPivotTable.PivotCache.Index = masterIndex

This sets the pivot cache of one pivot table to another.  It saves on memory and makes the pivot tables share the same data source.   Really handy for automating reporting.

However, there’s a trap here.   In Excel 2007, changing the pivot cache automatically removes the old pivot cache.   It works in 2003, and I suspect it keeps the old worksheets floating around.  This can create invalid indexes if you are not careful.

If you use the first worksheet as the master, that’s fine.  Its index will always be 1.  If you use a subsequent worksheet as a master, its index will change as you update subsequent reports with a lower index.

Pivot Cache Indexes Before Update:

  • 1
  • 2
  • [3] Master Index
  • 4

When you update 1, the indexes look like this:

Pivot Cache Indexes After Update:

  • 1
  • [2] Master Index
  • 3

Why?   Because the old index 1 got removed and the pivot caches got reindexed.

Fortunately, all of this is easily avoided.   Instead of tracking the index, track a pointer to the pivot table instead.

Dim MasterPivotTable as PivotTable

‘Insert your pivot table here

Set MasterPivotTable = Sheet1.PivotTable(1)

destinationPivotTable.PivotCache.Index = MasterPivotTable.PivotCache.Index

That’s how you swap a pivot cache properly, without the two days of a random 1004 error.

Fuck, I hate excel.