Updating data source on multiple pivot tables in Excel

Question

Is there an easy way to update a data source for multiple pivot tables in one Excel worksheet at a time?

All pivot tables refer to the same range, but I need to create a second sheet that has the same pivot tables, but access to a different named range.

Ideally, I would like to perform some kind of search and replace operation (for example, you can do according to formulas), and not manually update each individual pivot table.

Any suggestions?

Answer 1

The following VBA code will change the data source of all pivot tables on one sheet.

You will need to update the Sheet2 parameter to the sheet name with your new Data2 tables and the Data2 parameter for 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 

Answer 2

Assuming you're ready to use VBA, this may be relevant.

If you iterate over the collection of the pivot table on each sheet, you should use the method shown in this post to change the data source. The syntax should be very similar to using a named range, not a range of cells.

Answer 3

Here's a useful method adapted from Dynamically changing the range of pivot table data sources using this VBA macro

PivotTable . SourceData property that is set using the ChangePivotCache method, which accepts PivotCache . To create it, call ActiveWorkbook . PivotCaches create , which accepts SourceType and range as SourceData . Finally, after the update, be sure to call RefreshTable to apply the changes.

This is what it looks like in the code. Just replace Sheet1 wherever your data source is. This will automatically find every pivot table in your book and update it.

 Sub AdjustPivotDataRange() Dim pt As PivotTable, pc As PivotCache Dim dataSheet As Worksheet, ws As Worksheet Dim startPoint As Range, dataSource As Range, newRange As String ' get worksheet with data Set dataSheet = ThisWorkbook.Worksheets("Sheet1") ' Dynamically Retrieve Range Address of Data Set startPoint = dataSheet.Range("A1") Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell)) newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1) ' create new PivotCache Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=newRange) ' loop through all tables in all sheets For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables ' update pivot source and refresh pt.ChangePivotCache pc pt.RefreshTable Next pt Next ws End Sub