Automatically Update Excel Pivot Tables

Excel's Pivot Tables are fantastic for data analysis, but they have one small flaw - they require manual updates when the source data changes. If forgotten, this can have pretty catastrophic effects; I've known it to cause a few red faces!

There's a very simple solution for this, a small snippet of VBA code can be used to automatically update your Excel Pivot Tables. Simply run the below code using a convenient event trigger (for instance, by using the Worksheet_Activate() event trigger when your summary Dashboard is activated).

To learn how to use VBA, see our beginner's VBA tutorial here.

Sub updateAllPivots()

    Dim pvt As PivotTable

    Dim ws As Worksheet

    Dim counter As Integer

    Dim plural As String

    

    Application.ScreenUpdating = False

    

    For Each ws In ThisWorkbook.Worksheets

        For Each pvt In ws.PivotTables

            counter = counter + 1

            pvt.RefreshTable

        Next pvt

    Next ws

    If counter > 1 Then

        plural = "s"

    Else

        plural = ""

    End If

    MsgBox counter & " pivot tables" & plural & " updated."

End Sub

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.