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
Check the box Refresh data when opening the file
Now whenever you open the workbook, the pivot table gets refreshed.
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.
