How do I quickly align all my Pivot Tables to the same Pivot Cache?

 

  • 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!

Sub Allign_Source_Data()
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
Wks.Activate
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
PT.RefreshTable
Next PT
Next Wks
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
End Sub

Prerequisites:

  • 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!