Updating Multiple Pivot Tables

[rachelh]

Hi All,

I'm trying to create a macro to update multiple pivot tables in one worksheet with an expanded source data location. For example, I have added more data for a new quarter to the range that the pivot tables are reading from and I wanted to automate the process of updated the data source for all the pivot tables.

Any suggestions on how to do this?

I tried doing this (see below) but I was getting an error that "ChangePivotCache" is a "Sub or Function not defined":

Dim Sheet As Worksheet, Pivot As PivotTable

For Each Sheet In ThisWorkbook.Worksheets
For Each Pivot In Sheet.PivotTables
ChangePivotCache _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"....file path...." R1C1:R5164C42" _
, Version:=xlPivotTableVersion15)
Next
Next

 

[Domenic]

You'll need to qualify your reference...

[COLOR=#ff0000]Pivot.[/COLOR][FONT=tahoma]ChangePivotCache _[/FONT]

Also, each time you change the source data for a pivot table, a new pivot cache is created. Therefore, to keep only one pivot cache for all pivot tables, try...

Dim Sheet As Worksheet, Pivot As PivotTable
Dim iCacheIndex As Integer
Dim bCreated As Boolean

For Each Sheet In ThisWorkbook.Worksheets
    For Each Pivot In Sheet.PivotTables
        If Not bCreated Then
            Pivot.ChangePivotCache ActiveWorkbook.PivotCaches.Create...
            iCacheIndex = Pivot.CacheIndex
            bCreated = True
        Else
            Pivot.CacheIndex = iCacheIndex
        End If
    Next
Next

However, it would be much easier to convert your data into a Table or create a dynamic named range for the data, and use the table name or defined name as the source data for each pivot table . This way, the reference to the table or range will automatically adjust as data is added/removed.