Refreshing all the pivot tables in my excel workbook with a macro

Question

I have a workbook with 20 different pivot tables  Is there any easy way to find all the pivot tables and refresh them in VBA

User · Answer

You have a PivotTables collection on a the VB Worksheet object  So  a quick loop like this will work   Sub RefreshPivotTables       Dim pivotTable As PivotTable     For Each pivotTable In ActiveSheet PivotTables         pivotTable RefreshTable     Next End Sub   Notes from the trenches    Remember to unprotect any protected sheets before updating the PivotTable  Save often  I ll think of more and update in due course         Good luck

User · Answer

ActiveWorkbook RefreshAll refreshes everything  not only the pivot tables but also the ODBC queries  I have a couple of VBA queries that refer to Data connections and using this option crashes as the command runs the Data connections without the detail supplied from the VBA  I recommend the option if you only want the pivots refreshed  Sub RefreshPivotTables          Dim pivotTable As PivotTable        For Each pivotTable In ActiveSheet PivotTables              pivotTable RefreshTable        Next  End Sub

User · Answer

This VBA code will refresh all pivot tables charts in the workbook   Sub RefreshAllPivotTables    Dim PT As PivotTable Dim WS As Worksheet      For Each WS In ThisWorkbook Worksheets          For Each PT In WS PivotTables           PT RefreshTable         Next PT      Next WS  End Sub   Another non-programatic option is    Right click on each pivot table Select Table options Tick the  Refresh on open  option  Click on the OK button   This will refresh the pivot table each time the workbook is opened

User · Answer

In certain circumstances you might want to differentiate between a PivotTable and its PivotCache  The Cache has it s own refresh method and its own collections  So we could have refreshed all the PivotCaches instead of the PivotTables   The difference  When you create a new Pivot Table you are asked if you want it based on a previous table  If you say no  this Pivot Table gets its own cache and doubles the size of the source data  If you say yes  you keep your WorkBook small  but you add to a collection of Pivot Tables that share a single cache  The entire collection gets refreshed when you refresh any single Pivot Table in that collection  You can imagine therefore what the difference might be between refreshing every cache in the WorkBook  compared to refreshing every Pivot Table in the WorkBook

User · Answer

Yes   ThisWorkbook RefreshAll   Or  if your Excel version is old enough   Dim Sheet as WorkSheet  Pivot as PivotTable For Each Sheet in ThisWorkbook WorkSheets     For Each Pivot in Sheet PivotTables         Pivot RefreshTable         Pivot Update     Next Next

User · Answer

There is a refresh all option in the Pivot Table tool bar  That is enough  Dont have to do anything else   Press ctrl alt F5

User · Answer

Even we can refresh particular connection and in turn it will refresh all the pivots linked to it   For this code I have created slicer from table present in Excel   Sub UpdateConnection           Dim ServerName As String         Dim ServerNameRaw As String         Dim CubeName As String         Dim CubeNameRaw As String         Dim ConnectionString As String          ServerNameRaw   ActiveWorkbook SlicerCaches  Slicer ServerName   VisibleSlicerItemsList 1          ServerName   Replace Split ServerNameRaw       3                     CubeNameRaw   ActiveWorkbook SlicerCaches  Slicer CubeName   VisibleSlicerItemsList 1          CubeName   Replace Split CubeNameRaw       3                     If CubeName    All  Or ServerName    All  Then             MsgBox  Please Select One Cube and Server Name   vbOKOnly   Slicer Info          Else             ConnectionString   GetConnectionString ServerName  CubeName              UpdateAllQueryTableConnections ConnectionString  CubeName         End If     End Sub      Function GetConnectionString ServerName As String  CubeName As String          Dim result As String         result    OLEDB Provider MSOLAP 5 Integrated Security SSPI Persist Security Info True Initial Catalog    amp  CubeName  amp    Data Source    amp  ServerName  amp    MDX Compatibility 1 Safety Options 2 MDX Missing Member Mode Error Update Isolation Level 2            OLEDB Provider SQLOLEDB 1 Integrated Security SSPI Persist Security Info True Initial Catalog    amp  CubeName  amp    Data Source    amp  ServerName  amp    Use Procedure for Prepare 1 Auto Translate True Packet Size 4096 Use Encryption for Data False Tag with column collation when possible False          GetConnectionString   result     End Function      Function GetConnectionString ServerName As String  CubeName As String      Dim result As String     result    OLEDB Provider MSOLAP 5 Integrated Security SSPI Persist Security Info True Initial Catalog    amp  CubeName  amp    Data Source    amp  ServerName  amp    MDX Compatibility 1 Safety Options 2 MDX Missing Member Mode Error Update Isolation Level 2      GetConnectionString   result End Function  Sub UpdateAllQueryTableConnections ConnectionString As String  CubeName As String      Dim cn As WorkbookConnection     Dim oledbCn As OLEDBConnection     Dim Count As Integer  i As Integer     Dim DBName As String     DBName    Initial Catalog     CubeName      Count   0     For Each cn In ThisWorkbook Connections         If cn Name    ThisWorkbookDataModel  Then             Exit For         End If          oTmp   Split cn OLEDBConnection Connection               For i   0 To UBound oTmp  - 1             If InStr 1  oTmp i   DBName  vbTextCompare    1 Then                 Set oledbCn   cn OLEDBConnection                 oledbCn SavePassword   True                 oledbCn Connection   ConnectionString                 oledbCn Refresh                 Count   Count   1             End If         Next     Next      If Count   0 Then          MsgBox  Nothing to update   vbOKOnly   Update Connection      ElseIf Count  gt  0 Then         MsgBox  Update  amp  Refresh Connection Successfully   vbOKOnly   Update Connection      End If End Sub

User · Answer

The code    Private Sub Worksheet Activate       Dim PvtTbl As PivotTable         Cells EntireColumn AutoFit         For Each PvtTbl In Worksheets  Sales Details   PivotTables         PvtTbl RefreshTable         Next End Sub    works fine   The code is used in the activate sheet module  thus it displays a flicker glitch when the sheet is activated

User · Answer

Yes   ThisWorkbook RefreshAll   Or  if your Excel version is old enough   Dim Sheet as WorkSheet  Pivot as PivotTable For Each Sheet in ThisWorkbook WorkSheets     For Each Pivot in Sheet PivotTables         Pivot RefreshTable         Pivot Update     Next Next

User · Answer

In certain circumstances you might want to differentiate between a PivotTable and its PivotCache  The Cache has it s own refresh method and its own collections  So we could have refreshed all the PivotCaches instead of the PivotTables   The difference  When you create a new Pivot Table you are asked if you want it based on a previous table  If you say no  this Pivot Table gets its own cache and doubles the size of the source data  If you say yes  you keep your WorkBook small  but you add to a collection of Pivot Tables that share a single cache  The entire collection gets refreshed when you refresh any single Pivot Table in that collection  You can imagine therefore what the difference might be between refreshing every cache in the WorkBook  compared to refreshing every Pivot Table in the WorkBook

User · Answer

There is a refresh all option in the Pivot Table tool bar  That is enough  Dont have to do anything else   Press ctrl alt F5

User · Answer

If you are using MS Excel 2003 then go to view- Tool bar- Pivot Table From this tool bar we can do refresh by clicking   this symbol

User · Answer

This VBA code will refresh all pivot tables charts in the workbook   Sub RefreshAllPivotTables    Dim PT As PivotTable Dim WS As Worksheet      For Each WS In ThisWorkbook Worksheets          For Each PT In WS PivotTables           PT RefreshTable         Next PT      Next WS  End Sub   Another non-programatic option is    Right click on each pivot table Select Table options Tick the  Refresh on open  option  Click on the OK button   This will refresh the pivot table each time the workbook is opened

User · Answer

The code    Private Sub Worksheet Activate       Dim PvtTbl As PivotTable         Cells EntireColumn AutoFit         For Each PvtTbl In Worksheets  Sales Details   PivotTables         PvtTbl RefreshTable         Next End Sub    works fine   The code is used in the activate sheet module  thus it displays a flicker glitch when the sheet is activated

User · Answer

Even we can refresh particular connection and in turn it will refresh all the pivots linked to it   For this code I have created slicer from table present in Excel   Sub UpdateConnection           Dim ServerName As String         Dim ServerNameRaw As String         Dim CubeName As String         Dim CubeNameRaw As String         Dim ConnectionString As String          ServerNameRaw   ActiveWorkbook SlicerCaches  Slicer ServerName   VisibleSlicerItemsList 1          ServerName   Replace Split ServerNameRaw       3                     CubeNameRaw   ActiveWorkbook SlicerCaches  Slicer CubeName   VisibleSlicerItemsList 1          CubeName   Replace Split CubeNameRaw       3                     If CubeName    All  Or ServerName    All  Then             MsgBox  Please Select One Cube and Server Name   vbOKOnly   Slicer Info          Else             ConnectionString   GetConnectionString ServerName  CubeName              UpdateAllQueryTableConnections ConnectionString  CubeName         End If     End Sub      Function GetConnectionString ServerName As String  CubeName As String          Dim result As String         result    OLEDB Provider MSOLAP 5 Integrated Security SSPI Persist Security Info True Initial Catalog    amp  CubeName  amp    Data Source    amp  ServerName  amp    MDX Compatibility 1 Safety Options 2 MDX Missing Member Mode Error Update Isolation Level 2            OLEDB Provider SQLOLEDB 1 Integrated Security SSPI Persist Security Info True Initial Catalog    amp  CubeName  amp    Data Source    amp  ServerName  amp    Use Procedure for Prepare 1 Auto Translate True Packet Size 4096 Use Encryption for Data False Tag with column collation when possible False          GetConnectionString   result     End Function      Function GetConnectionString ServerName As String  CubeName As String      Dim result As String     result    OLEDB Provider MSOLAP 5 Integrated Security SSPI Persist Security Info True Initial Catalog    amp  CubeName  amp    Data Source    amp  ServerName  amp    MDX Compatibility 1 Safety Options 2 MDX Missing Member Mode Error Update Isolation Level 2      GetConnectionString   result End Function  Sub UpdateAllQueryTableConnections ConnectionString As String  CubeName As String      Dim cn As WorkbookConnection     Dim oledbCn As OLEDBConnection     Dim Count As Integer  i As Integer     Dim DBName As String     DBName    Initial Catalog     CubeName      Count   0     For Each cn In ThisWorkbook Connections         If cn Name    ThisWorkbookDataModel  Then             Exit For         End If          oTmp   Split cn OLEDBConnection Connection               For i   0 To UBound oTmp  - 1             If InStr 1  oTmp i   DBName  vbTextCompare    1 Then                 Set oledbCn   cn OLEDBConnection                 oledbCn SavePassword   True                 oledbCn Connection   ConnectionString                 oledbCn Refresh                 Count   Count   1             End If         Next     Next      If Count   0 Then          MsgBox  Nothing to update   vbOKOnly   Update Connection      ElseIf Count  gt  0 Then         MsgBox  Update  amp  Refresh Connection Successfully   vbOKOnly   Update Connection      End If End Sub

User · Answer

ActiveWorkbook RefreshAll refreshes everything  not only the pivot tables but also the ODBC queries  I have a couple of VBA queries that refer to Data connections and using this option crashes as the command runs the Data connections without the detail supplied from the VBA  I recommend the option if you only want the pivots refreshed  Sub RefreshPivotTables          Dim pivotTable As PivotTable        For Each pivotTable In ActiveSheet PivotTables              pivotTable RefreshTable        Next  End Sub

User · Answer

There is a refresh all option in the Pivot Table tool bar  That is enough  Dont have to do anything else   Press ctrl alt F5

User · Answer

I have use the command listed below in the recent past and it seems to work fine   ActiveWorkbook RefreshAll   Hope that helps

User · Answer

You have a PivotTables collection on a the VB Worksheet object  So  a quick loop like this will work   Sub RefreshPivotTables       Dim pivotTable As PivotTable     For Each pivotTable In ActiveSheet PivotTables         pivotTable RefreshTable     Next End Sub   Notes from the trenches    Remember to unprotect any protected sheets before updating the PivotTable  Save often  I ll think of more and update in due course         Good luck

User · Answer

I have use the command listed below in the recent past and it seems to work fine   ActiveWorkbook RefreshAll   Hope that helps

User · Answer

You have a PivotTables collection on a the VB Worksheet object  So  a quick loop like this will work   Sub RefreshPivotTables       Dim pivotTable As PivotTable     For Each pivotTable In ActiveSheet PivotTables         pivotTable RefreshTable     Next End Sub   Notes from the trenches    Remember to unprotect any protected sheets before updating the PivotTable  Save often  I ll think of more and update in due course         Good luck

User · Answer

This VBA code will refresh all pivot tables charts in the workbook   Sub RefreshAllPivotTables    Dim PT As PivotTable Dim WS As Worksheet      For Each WS In ThisWorkbook Worksheets          For Each PT In WS PivotTables           PT RefreshTable         Next PT      Next WS  End Sub   Another non-programatic option is    Right click on each pivot table Select Table options Tick the  Refresh on open  option  Click on the OK button   This will refresh the pivot table each time the workbook is opened

User · Answer

There is a refresh all option in the Pivot Table tool bar  That is enough  Dont have to do anything else   Press ctrl alt F5

User · Answer

This VBA code will refresh all pivot tables charts in the workbook   Sub RefreshAllPivotTables    Dim PT As PivotTable Dim WS As Worksheet      For Each WS In ThisWorkbook Worksheets          For Each PT In WS PivotTables           PT RefreshTable         Next PT      Next WS  End Sub   Another non-programatic option is    Right click on each pivot table Select Table options Tick the  Refresh on open  option  Click on the OK button   This will refresh the pivot table each time the workbook is opened

User · Answer

Yes   ThisWorkbook RefreshAll   Or  if your Excel version is old enough   Dim Sheet as WorkSheet  Pivot as PivotTable For Each Sheet in ThisWorkbook WorkSheets     For Each Pivot in Sheet PivotTables         Pivot RefreshTable         Pivot Update     Next Next

User · Answer

Yes   ThisWorkbook RefreshAll   Or  if your Excel version is old enough   Dim Sheet as WorkSheet  Pivot as PivotTable For Each Sheet in ThisWorkbook WorkSheets     For Each Pivot in Sheet PivotTables         Pivot RefreshTable         Pivot Update     Next Next

User · Answer

You have a PivotTables collection on a the VB Worksheet object  So  a quick loop like this will work   Sub RefreshPivotTables       Dim pivotTable As PivotTable     For Each pivotTable In ActiveSheet PivotTables         pivotTable RefreshTable     Next End Sub   Notes from the trenches    Remember to unprotect any protected sheets before updating the PivotTable  Save often  I ll think of more and update in due course         Good luck

User · Answer

If you are using MS Excel 2003 then go to view- Tool bar- Pivot Table From this tool bar we can do refresh by clicking   this symbol

[excel] Refreshing all the pivot tables in my excel workbook with a macro

Examples related to excel

Examples related to vba

Examples related to refresh