Excel automatically refresh pivot table

This Excel tutorial explains how to automatically refresh pivot table using Macro / VBA and Pivot Table Options.

Excel automatically refresh pivot table

You may have a pivot table that links to data in the same workbook or external data, and you may want to refresh pivot table automatically in order to see the most updated data. In this tutorial I will show several ways to automatically refresh pivot table.

Method 1 – Automatically refresh pivot table when opening the file

This is the simplest way to automatically refresh pivot table. Right click on the pivot table, select Pivot Table Options

automatically refresh pivot table 02

 

Check the box Refresh data when opening the file

Now whenever you open the workbook, the pivot table gets refreshed.

automatically refresh pivot table 01

Method 2 – Automatically refresh pivot table when data source updates

With the help of VBA Change Event, whenever the data source worksheet has data change, all pivot table in the workbook are refreshed.

Press ALT+F11 to enter into VBE > double click on the data source workbook > copy and paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.RefreshAll
End Sub

Alternatively, you can precisely indicate which pivot table you want to refresh instead of refreshing all. Click here to understand how to use VBA to refresh pivot table.

Private Sub Worksheet_Change(ByVal Target As Range)
    Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Method 3 – Automatically refresh pivot table at specific time interval

With the help of Application.OnTime Method, automatic refresh can be triggered at specific time interval, which is good if you don’t have a lot of data to refresh.

Press ALT+F11 to enter into VBE > insert a new Module > copy and paste the below code

Sub my_onTime()
    Application.OnTime Now + TimeValue("00:00:10"), "my_Procedure"
End Sub

Sub my_Procedure()
    ThisWorkbook.RefreshAll
    my_onTime
End Sub

In the above example, all pivot tables are refreshed automatically every 10 seconds. You can define your own refresh time interval.

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *