Updating data source on multiple pivot tables within Excel

Question

Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time?

All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range.

Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand.

Any suggestions?

Answer 1

Assuming you're willing to use VBA, this might be relevant.

If you iterate through the PivotTable collection on each sheet, you should be able to use the method shown in that post to amend the data source. The syntax should be very similar to use a named range rather than a range of cells.

Answer 2

The following VBA code will change the data source of all pivot tables on a single worksheet.

You will need to update the "Sheet2" parameter to the name of the sheet with your new pivot tables and the "Data2" parameter to your new named range.

Sub Change_Pivot_Source()

Dim pt As PivotTable

For Each pt In ActiveWorkbook.Worksheets("Sheet2").PivotTables
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
    (SourceType:=xlDatabase, SourceData:="Data2")
Next pt

End Sub