- You need to change the source data of all your pivot tables from a dataset stored in your workbook to an ODBC/OLEDB connection?
Don’t feel like doing it manually?
That’s completely understandable!
As you see above, doing it manually in the first case can be too time consuming…
Manual adjustment is not even technically possible in the second case, in which scenario every pivot table in your workbook will have to be re-built from scratch 😦
Luckily, you can use the VBA snippet below to get the chore quickly out of the way!
|Dim Wks As Worksheet|
|Dim PT As PivotTable|
|Application.DisplayAlerts = False|
|'The code below can also change the pivot table source from interanl (e.g. dataset in wbk)|
|'to external (e.g OLEDB, ODBC connection)|
|For Each Wks In ActiveWorkbook.Worksheets|
|For Each PT In ActiveSheet.PivotTables|
|PT.CacheIndex = Sheets(1).PivotTables(1).CacheIndex|
|'1 in Sheets(1) refers to the position of the sheet in the wbk|
|'1 in PivotTables(1) refers to the first pivot table in the active worksheet|
|Application.DisplayAlerts = True|
|'The code above could generate error messages if:|
|'1) a worksheet has multiple pivot tables in it|
|'2) the workbook and/or worksheets are password-protected|
- Select one of your pivot tables to whose pivot cache you’d like to align the rest
- Make sure that either the worksheet, which holds the Pivot Table, is the first one in your workook or change the sheet index /i.e. Sheets(1)/ in the VBA code above to reflect the workbook position of your sheet
NB! If you have workbook and/or worksheet protection in place, you will need to ensure that those are off befoe trying to run the code
Happy VBA coding!