Refresh Excel Pivot Table and Cache of PivotTable, using VBA
Refresh Data in a PivotTable report: You have different ways to refresh and update data in a PivotTable report with any changes made in the source data. This section explains how to update or refresh a PivotTable report, with VBA code.
Related Links:
1. Create an Excel Pivot Table report using VBA
2. Referencing an Excel Pivot Table Range using VBA
3. Reference Pivot Fields and Pivot Items in an Excel Pivot Table, using VBA
4. Excel Pivot Table Address, Location, Move & Copy using VBA
5. Excel Pivot Table Layout and Design, using VBA
6. Excel Pivot Table Properties & Settings, using VBA
7. Refresh Excel Pivot Table and Cache of PivotTable, using VBA
8. Excel Pivot Tables Grouping: Group Items, Group Data and Group Date Values, using VBA
9. Excel Pivot Tables: Sort Fields, Values & Dates, use Custom Lists, with VBA
10. Excel Pivot Tables: Filter Data, Items, Values & Dates using VBA
11. Excel Pivot Tables: Summary Functions, Custom Calculations & Value Field Settings, using VBA
12. Excel Pivot Tables: Insert Calculated Fields & Calculated Items, Create Formulas using VBA
13. Create & Customize Excel Pivot Table Charts, using VBA
Refer complete Tutorial on using Pivot Tables in Excel Window (user interface):
Create and Customize a Pivot Table report
---------------------------------------------------------------------------------------------------------------
Contents:
Refresh the Cache of the Pivot Table using the PivotCache.Refresh Method
Return the Date when PivotTable was last refreshed, and Name of the user who refreshed
---------------------------------------------------------------------------------------------------------------
Refresh the Cache of the Pivot Table using the PivotCache.Refresh Method
Refresh all Pivot Tables in a workbook using the PivotCache method. In this example each PivotTable report (starting from the first) in each worksheet is refreshed. Note that each Pivot Table report has one cache only. See below code:
Sub PivotTableRefresh1()
Dim PvtTbl As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
n = 0
For Each PvtTbl In ws.PivotTables
n = n + 1
Next
If n >= 1 Then
For i = 1 To n
ws.PivotTables(i).PivotCache.Refresh
Next i
End If
Next
End Sub
Refresh all PivotTable Caches in the workbook:
For Each PivotCache In ActiveWorkbook.PivotCaches
PivotCache.Refresh
Next
Refresh all PivotTable Caches in a worksheet:
For i = 1 To Worksheets("Sheet1").PivotTables.Count
Worksheets("Sheet1").PivotTables(i).PivotCache.Refresh
Next i
Refresh only the second PivotTable Cache in a worksheet:
Worksheets("Sheet1").PivotTables(2).PivotCache.Refresh
Refresh all PivotTable Caches in the workbook: Using PivotCaches(index), index being the PivotTable cache number. This returns a single PivotCache from the collection of memory caches in a workbook. Note that each Pivot Table report has one cache only. See below code:
For i = 1 To ActiveWorkbook.PivotCaches.Count
ActiveWorkbook.PivotCaches(i).Refresh
Next i
PivotTable.RefreshTable Method refreshes and updates the PivotTable report with all information from the data source
Refresh a specific Pivot Table in a worksheet:
Worksheets("Sheet1").PivotTables("PivotTable1").RefreshTable
Refresh all Pivot Tables in a worksheet:
Dim PvtTbl As PivotTable
For Each PvtTbl In Worksheets("Sheet1").PivotTables
PvtTbl.RefreshTable
Next
Refresh specific Pivot Tables in a worksheet:
Dim PvtTbl As PivotTable
For Each PvtTbl In Worksheets("Sheet1").PivotTables
If MsgBox("Refresh " & PvtTbl & "?", vbYesNo) = vbYes Then
PvtTbl.RefreshTable
End If
Next
Refresh all Pivot Tables in a workbook:
Dim PvtTbl As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each PvtTbl In ws.PivotTables
PvtTbl.RefreshTable
Next
Next
Automatically refresh Pivot Tables in all worksheets on opening a workbook, using the PivotTable.RefreshTable Method
Code to automatically refresh PivotTable reports in all worksheets, when the workbook opens, using the Workbook_Open() event handler:
Private Sub Workbook_Open()
'Automatically refresh Pivot Tables in all worksheets on opening a workbook. Use the Workbook_Open() event handler to execute code when the workbook opens which will automatically reload and refresh the PivotTable reports from their respective source data.
'To create a workbook event: use the Visual Basic Editor -> in the Project Explorer, double click on "ThisWorkbook" (under 'Microsoft Excel Objects' which is under the name of your workbook) -> in the Code
window, select "Workbook" from the left-side "General" drop-down menu and then select the relevant event from the right-side "Declarations" drop-down menu. Workbook events code must be placed in the code
module for the ThisWorkbook object. If you select "Open" here, the code window will show:
'Private Sub Workbook_Open()
'End Sub
Dim PvtTbl As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each PvtTbl In ws.PivotTables
PvtTbl.RefreshTable
Next
Next
End Sub
Return the Date when PivotTable was last refreshed, and Name of the user who refreshed
Return the Date when PivotTable was last refreshed, using the PivotTable.RefreshDate Property:
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").RefreshDate
Return the name of the user who last refreshed the PivotTable, using the PivotTable.RefreshName Property:
MsgBox Worksheets("Sheet1").PivotTables("PivotTable1").RefreshName
