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.

Insert the VBA code snippet into Sheet1 within 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.

Have Your Say

Δ