Automatically update Pivot Tables in Excel
A simple VBA script that refreshes (updates) all Pivot Tables within an Excel workbook after any change to a worksheet.
Pivot Tables are one of the strongest features in Microsoft Excel, but one of their drawbacks is their requirement for a refresh every time data from a worksheet is updated. Failure to perform this manual task will result in Pivot Tables displaying old data even when worksheets have been updated.
If a Pivot Table is used to summarise data which is then returned to a workbook via a
GETPIVOTDATA function, the need to refresh Pivot Tables becomes all that much more important.
The following VBA script will automatically update all Pivot Tables in an Excel workbook when the value of any cell in a specified worksheet is changed.
Private Sub Worksheet_Change(ByVal Target As Range) 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
To enable the script, simply paste the code into the
Sheet1 (*) code page under the ‘Microsoft Excel Objects’ tree in Visual Basic for Applications.
The Pivot Tables should now update every time data is changed within
Sheet1. Add this script to as many sheets as required in any workbook containing Pivot Tables.