Print

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

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

PivotTable.RefreshTable Method refreshes and updates the PivotTable report with all information from the data source

Automatically refresh Pivot Tables in all worksheets on opening a workbook, using the PivotTable.RefreshTable 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