I Crafted My Own Excel Feature After a Year-Long Wait for An Official Release

Neglecting to refresh the PivotTables in an Excel report can render it inaccurate. While Microsoft introduced an Auto Refresh feature last year, it has yet to be implemented in my version of Excel. Frustrated by the wait, I decided to create my own solution.

I’ve developed a VBA macro that resides in my Personal Macro Workbook. This macro adds a button to the Quick Access Toolbar (QAT) that enables users to refresh PivotTables automatically at set intervals in any workbook. Additionally, it allows for easy toggling of this functionality whenever needed. While working on this, I personalized the tool to better fit my Excel workflow.

My VBA macro introduces automatic PivotTable refreshing to Excel

A one-click toggle keeps reports up to date

Microsoft’s Auto Refresh feature uses a data-source method. It’s set to activate by default for new PivotTables, governed by a single setting for all its instances tied to the same source. I preferred a more tailored approach: a toggle for a specific workbook that would automatically refresh all its PivotTables.

My tool operates simply:

• Clicking the QAT button activates Live PivotTables.
• The PivotTables in the active workbook refresh instantly upon activation.
• They continue to refresh at the designated interval.
• Pressing the button again disables the feature.

I named it “Live PivotTables” to emphasize its toggling nature: turn it on for automatic updates, and switch it off when no longer needed. When activated, a message confirms which workbook is under surveillance along with the refresh interval—especially handy when multiple Excel files are open simultaneously.

This macro is intentionally focused; it refreshes only PivotTables without affecting Excel’s “Refresh All” function, which handles Power Query updates and other external connections.

I programmed the macro to track the targeted workbook

Maintaining control over automatic updates

A key consideration in the design was managing scenarios with multiple open workbooks. I wanted the refresh to be intentional: clicking the button should specify the targeted workbook.

The macro tracks the active workbook’s name:

Public mWorkbookName As String

Upon enabling Live PivotTables, it records the active workbook:

mWorkbookName = wb.Name

Subsequent scheduled refreshes are directed exclusively at that workbook. The same name is used when disabling Live PivotTables, ensuring the confirmation message reflects which workbook was affected. An additional safeguard disables the macro if the monitored workbook is closed, preventing it from referencing a non-existent workbook.

A timer automates the refresh of PivotTables

Converting manual action into a scheduled task

The next objective was to automate the refresh process continuously without manual clicks. I utilized Excel’s built-in VBA scheduling feature, Application.OnTime. Initially, I set a parameter to determine the refresh frequency:

Public Const REFRESH_INTERVAL_SECONDS As Long = 300

This is set to five minutes (300 seconds) by default, but can be adjusted easily. During trials, I reduced it to 15 seconds for testing the refresh functionality quickly.

The macro schedules the next refresh as follows:

Application.OnTime _ 
    EarliestTime:=mNextRun, _ 
    Procedure:="RunScheduledRefresh"

When the scheduled time arrives, Excel executes the refresh procedure. Notably, the next refresh is only scheduled after the previous one is complete, ensuring predictable behavior even in workbooks where refresh durations vary.

The macro provides running status feedback

Incorporating feedback while keeping Excel’s workflow intact

An automated process necessitates a way to inform the user about its status. The Live PivotTables feature provides feedback in two straightforward forms: a confirmation message upon activation and a temporary status bar update during the refresh.

MsgBox "Live PivotTables is now ON." & vbCrLf & vbCrLf & _
       mWorkbookName & " will refresh every " & _
       REFRESH_INTERVAL_SECONDS & " seconds.", _
       vbInformation

Application.StatusBar = "Live PivotTables: Refreshing..."

Option Explicit

Public Const REFRESH_INTERVAL_SECONDS As Long = 300

Public mIsEnabled As Boolean
Public mNextRun As Date
Public mWorkbookName As String

Public Sub ToggleLivePivotTables()
    If mIsEnabled Then
        If Not WorkbookExists(mWorkbookName) Then
            DisableLivePivotTablesSilently
            EnableLivePivotTables
        Else
            DisableLivePivotTables
        End If
    Else
        EnableLivePivotTables
    End If
End Sub

Public Sub EnableLivePivotTables()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    If wb Is Nothing Then
        MsgBox "No workbook is open.", vbExclamation
        Exit Sub
    End If
    If mIsEnabled Then
        MsgBox "Live PivotTables is already enabled for " & _
               mWorkbookName & ".", _
               vbInformation
        Exit Sub
    End If
    mWorkbookName = wb.Name
    mIsEnabled = True
    If Not RefreshPivotTables Then
        DisableLivePivotTables
        MsgBox "Live PivotTables failed. The feature has been turned off.", _
               vbCritical
        Exit Sub
    End If
    ScheduleNextRefresh
    MsgBox "Live PivotTables is now ON." & vbCrLf & vbCrLf & _
           mWorkbookName & " will refresh every " & _
           REFRESH_INTERVAL_SECONDS & " seconds.", _
           vbInformation
End Sub

Public Sub DisableLivePivotTables()
    CancelNextRefresh
    mIsEnabled = False
    If mWorkbookName <> "" Then
        MsgBox "Live PivotTables has been disabled for " & _
               mWorkbookName & ".", _
               vbInformation
    End If
    mWorkbookName = ""
    Application.StatusBar = False
End Sub

Public Sub DisableLivePivotTablesSilently()
    CancelNextRefresh
    mIsEnabled = False
    mWorkbookName = ""
    Application.StatusBar = False
End Sub

Public Sub RunScheduledRefresh()
    If Not mIsEnabled Then Exit Sub
    If Not WorkbookExists(mWorkbookName) Then
        DisableLivePivotTablesSilently
        Exit Sub
    End If
    If Not RefreshPivotTables Then
        CancelNextRefresh
        mIsEnabled = False
        MsgBox "Live PivotTables failed. The feature has been turned off.", _
               vbCritical
        mWorkbookName = ""
        Exit Sub
    End If
    ScheduleNextRefresh
End Sub

Private Function RefreshPivotTables() As Boolean
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim previousScreenUpdating As Boolean
    Dim waitUntil As Date
    On Error GoTo Failed
    Set wb = Workbooks(mWorkbookName)
    previousScreenUpdating = Application.ScreenUpdating
    Application.StatusBar = "Live PivotTables: Refreshing..."
    DoEvents
    Application.ScreenUpdating = False

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
    Application.ScreenUpdating = previousScreenUpdating
    waitUntil = Now + TimeValue("00:00:02")
    
    Do While Now < waitUntil
    Loop
    
    On Error GoTo 0
    mNextRun = 0
End Function

Private Function WorkbookExists(ByVal workbookName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(workbookName)
    WorkbookExists = Not wb Is Nothing
    On Error GoTo 0
End Function