Change data source in multiple pivot tables

[rosiejameson]

I have a file containing approximatly 150 pivot tables, all sourced to another work book.

Is there a way in which I can change the data source for all of these pivot tables in one go? The data source is a different workbook that the one the pivot tables are in.

 

[Domenic]

Try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit
[/COLOR]
[COLOR=darkblue]Sub[/COLOR] ChangeDataSourceForAllPivotTables()

    [COLOR=darkblue]Dim[/COLOR] wb                  [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] ws                  [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] pt                  [COLOR=darkblue]As[/COLOR] PivotTable
    [COLOR=darkblue]Dim[/COLOR] sSourceData         [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] ErrHandler
    
    sSourceData = "'C:\Users\Domenic\Desktop\[Book2.xlsm]Sheet1'!A1:C500" [COLOR=green]'change accordingly
[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wb = ActiveWorkbook
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] pt [COLOR=darkblue]In[/COLOR] ws.PivotTables
            pt.ChangePivotCache wb.PivotCaches.Create(xlDatabase, sSourceData)
            pt.RefreshTable
        [COLOR=darkblue]Next[/COLOR] pt
    [COLOR=darkblue]Next[/COLOR] ws
    
ExitTheSub:
    
    [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] ws = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] pt = [COLOR=darkblue]Nothing[/COLOR]
    
    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    [COLOR=darkblue]Resume[/COLOR] ExitTheSub
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!