How to Update Pivot Table Data Sources on New & Copied Workbooks with VBA

The Problem

A while back, I was trying write a macro that takes a master sales file containing sales data for a large amount of sales people, and for each sales person, create individual copies of the master file with everyone else's sales data trimmed out.

I had gotten everything to work except the Pivot Table data sources wouldn't update. On any copies made from the original workbook, the pivot data source stayed linked to the original master instead of updating to the new workbook. This broke every pivot table and pivot chart and essentially made the copies worthless.

I tried just about everything before finally finding a solution that worked.

Pivot Cache vs. Pivot Table

As a quick rundown on the difference between Pivot Caches and Pivot Tables, a Pivot Cache holds a copy of the data table, and when a Pivot Table connects to the Cache, any changes made to the Pivot Table, actually get made to the Cache, preserving the original data.

This link from Pivot to Cache, is what was breaking when copying the workbook. The Pivot Tables were updating but the cache that the tables were connecting to stayed linked to the original workbook.

This is what was happening; as you can see under the Data source, the table that it was connecting to was in an outside "SalesWorkbook" workbook, instead of the new one:

This is what it should look like, using the SalesDataTable that is within its own workbook, instead of the master's sales table:

The Fix

What finally worked was renaming each individual Pivot Cache. This sub loops through every Pivot Table in the workbook, pulls in the full data source name (in this case 'SalesWorkbook'!SalesDataTable), then puts anything located to the right of the "!" character into an array, which is then used to rename the source data for all Pivot Caches.

This essentially renames every Pivot source from 'SalesWorkbook'!SalesDataTable , into this: SalesDataTable . Ensuring that the caches and tables are correctly updated from the data within the active workbook.

Here is the full code snippet:

Sub updatePivotTables (wb As Workbook)

    Dim pt As PivotTable, ws As Worksheet, array
    For Each ws In wb.Sheets
        For Each pt In ws.PivotTables
            array = Split(pt.PivotCache.SourceData, "!")
            If UBound(array) = 1 Then
               pt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=array(1))
            End If
         Next
     Next
     
End Sub

By calling this sub inside the loop that generated the individualized reports, the Pivot Tables all link to the correct table and cache inside the new workbook.

Problem solved!