Updating data source on multiple pivot tables within Excel

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开发者_JAVA百科 by hand.

Any suggestions?

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

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.

I combined both the above codes and now with the below code you can use either table or range reference for your source data. All you have to do is put in your source data by replacing the * sign and you're good to go.

Sub Change_Pivot_Source()

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

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

End Sub

Adapted from Dynamically Change A Pivot Table's Data Source Range With This VBA Macro Code:

1. The PivotTable.SourceData property can be set which is set via the ChangePivotCache method.
2. To create a new PivotCache, call ActiveWorkbook.PivotCaches.Create
3. You'll need to pass in a SourceType and a Range as SourceData.
4. Finally, once updated, make sure to call RefreshTable to apply the changes.

Here's an example that will automatically find every pivot table in your workbook 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 = ActiveWorkbook.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

Just replace "Sheet1" with wherever your datasource is located.