Created
March 3, 2018 13:56
-
-
Save ateneva/e396a8ef5bcf03db4c1fbc9ba0cbe17c to your computer and use it in GitHub Desktop.
How do I quickly align all my Pivot Tables to the same Pivot Cache?
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment