VBA to change pivot source data

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