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!

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!