This morning’s post was prompted by a question posted at MrExcel (here) regarding changing the source data for multiple pivot tables to refer to a different worksheet range. The stumbling block turned out to be that when you have multiple pivot tables that use the same pivot cache, you cannot simply change the SourceData property of the existing cache as you can if there’s only one pivot table. The workaround is to create a new cache and then assign that to all the pivot tables:
Sub ChangeCaches(sNewSource As String)
' sample to change multiple pivots based off the same Excel Range-based pivotcache
Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
Dim bCreated As Boolean
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
If Not bCreated Then
' this only adds a new cache on the first run through
' on subsequent passes, the pivot tables are simply assigned to the new cache
' if multiple caches are desired, simply repeat this part for each pivot table.
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=sNewSource, Version:=xlPivotTableVersion14)
Set pc = pt.PivotCache
bCreated = True
Else
If pt.CacheIndex <> pc.Index Then pt.CacheIndex = pc.Index
End If
Next pt
Next ws
End Sub
' sample to change multiple pivots based off the same Excel Range-based pivotcache
Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
Dim bCreated As Boolean
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
If Not bCreated Then
' this only adds a new cache on the first run through
' on subsequent passes, the pivot tables are simply assigned to the new cache
' if multiple caches are desired, simply repeat this part for each pivot table.
pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=sNewSource, Version:=xlPivotTableVersion14)
Set pc = pt.PivotCache
bCreated = True
Else
If pt.CacheIndex <> pc.Index Then pt.CacheIndex = pc.Index
End If
Next pt
Next ws
End Sub
Hopefully that may prove useful to others.
Note: trying to create the PivotCache before the loop and then assigning it to the pivot tables doesn’t work for some reason. The cache ends up with an Index of zero and is unusable.