To refresh every PivotTable in the workbook that contains your VBA, run a macro that loops through each worksheet and calls RefreshTable. To run the refresh whenever the workbook opens, put a separate Workbook_Open event in the ThisWorkbook module; the examples below show both approaches and when each is appropriate.
Refresh every PivotTable in the workbook
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the following procedure into the new module, and run RefreshAllPivotTables. The macro checks every worksheet in the workbook that contains the code and refreshes each PivotTable it finds.
Sub RefreshAllPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
Dim oldScreenUpdating As Boolean
Dim oldEnableEvents As Boolean
Dim failure As String
' Preserve Excel's current application settings.
oldScreenUpdating = Application.ScreenUpdating
oldEnableEvents = Application.EnableEvents
On Error GoTo Failed
Application.ScreenUpdating = False
Application.EnableEvents = False
' Refresh each PivotTable in this workbook.
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
GoTo CleanUp
Failed:
failure = Err.Description
CleanUp:
' Restore settings even if a refresh fails.
Application.ScreenUpdating = oldScreenUpdating
Application.EnableEvents = oldEnableEvents
If Len(failure) > 0 Then
MsgBox "PivotTable refresh stopped: " & failure, vbExclamation
End If
End Sub
In the editor, click inside the procedure and press F5 to run it, or return to Excel and use Developer > Macros to select the macro. The code uses ThisWorkbook, meaning the workbook where the macro is stored. That is safer than ActiveWorkbook when other files are open, because the active workbook can change. If you store the code in your Personal Macro Workbook instead, adjust the reference to identify the workbook you intend to refresh.
How PivotTable refresh works
A PivotTable displays a summarized view of source data. Refreshing tells Excel to read its source again and update the report. The source might be a range, an Excel table, an external connection, or the workbook Data Model. A refresh updates the PivotTable’s results; it does not necessarily repair a source range that excludes new records or make an inaccessible external source available.
PivotTables use a PivotCache to hold source data for the report. Multiple PivotTables can share a cache, so refreshing reports one at a time may repeat work when many reports share the same source. For a modest workbook, the loop above is easy to understand and maintain. For a large workbook or one with many reports, consider refreshing a cache once and checking the result, or using the workbook’s refresh-all behavior if connections and queries also need updating.
The macro turns off screen updating while it runs to reduce visual flicker, and disables events so that a refresh does not trigger other event procedures unexpectedly. It saves and restores the prior settings, including after an error. It does not suppress every possible Excel prompt, and a source that requires credentials, is unavailable, or has an invalid range can still cause a refresh to fail.
Run the refresh when the workbook opens
If the goal is to show updated reports whenever someone opens the file, use Excel’s workbook-open event. In the Visual Basic Editor, find the target workbook in Project Explorer, double-click ThisWorkbook, and place this event procedure there—not in a standard module. It calls Excel’s Refresh All operation when the workbook opens.
Private Sub Workbook_Open()
Dim wb As Workbook
' Refresh workbook connections and PivotTables at opening.
Set wb = ThisWorkbook
wb.RefreshAll
End Sub
For a file containing only local PivotTables, the first macro provides more explicit control over which reports refresh. RefreshAll is useful when the workbook also contains queries or data connections that should update. Some external data refreshes can run in the background, so a PivotTable dependent on a query may not be ready at the exact moment the event procedure returns. If that timing matters, review the connection’s refresh settings and test the sequence using a copy of the workbook rather than assuming every connection completes synchronously.
To make the event run, save the file as an Excel Macro-Enabled Workbook (.xlsm) and allow macros when opening it, subject to your organization’s security policy. A workbook saved as .xlsx cannot retain VBA code. Excel for the web can open and edit many workbook contents but cannot run VBA macros; open the file in the desktop Excel application to use them.
Refresh after source data changes
“Automatically” can also mean refreshing immediately after someone edits the source data. A worksheet-change event can do that, but it should be used carefully: pasting hundreds of rows may trigger the event, and refreshing after every individual edit can make data entry slow. Put this event in the worksheet module that contains the source data, replacing Sheet1 with the actual source sheet if you refer to a specific sheet elsewhere.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim pt As PivotTable
' Ignore edits outside the source data area.
If Intersect(Target, Me.Range("A:D")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
' Refresh PivotTables on this worksheet.
For Each pt In Me.PivotTables
pt.RefreshTable
Next pt
CleanUp:
Application.EnableEvents = True
End Sub
This sample only refreshes PivotTables on the same sheet as the changed cells. If the PivotTable is on a different sheet, change the loop to refer to that sheet, or call a separate procedure that refreshes the workbook’s reports. The watched range A:D is an example, not a universal source range; narrow it to the actual columns that affect the PivotTable. If the source is an Excel table, a structured range or a table-specific approach can make the event’s scope easier to maintain.
A worksheet-change event responds to direct edits and pasted values. It does not run merely because a formula result changes during recalculation. For formula-driven sources, consider a controlled refresh button, a scheduled refresh, or a suitable calculation event rather than assuming Worksheet_Change catches every update. Also consider refreshing once after a multi-cell paste, not repeatedly as a user types through a series of edits.
Choose an approach that matches the source
- Local range or table: Use the all-PivotTables macro for a manual refresh, or the open event if users should see current results when they open the file. Make sure the PivotTable source includes newly added rows.
- Power Query or other workbook connection: Use
RefreshAllwhen the query or connection must update along with its dependent PivotTables. Confirm whether background refresh affects the order or timing of updates. - External database or online source: Check that the computer has access and any required credentials. VBA cannot bypass source permissions or network availability.
- Several PivotTables from one source: Keep the code simple initially, then investigate shared caches and refresh duration if performance becomes a problem.
- Data Model or OLAP source: Test the chosen method on a copy. These sources can have different refresh behavior from a PivotTable based on a plain worksheet range.
For a one-time refresh without VBA, click inside the PivotTable and choose PivotTable Analyze > Refresh. To update workbook connections and reports together, use Data > Refresh All. These built-in commands are often preferable if a user can refresh manually and there is no need for event-driven automation.
Make the source range reliable
A common misconception is that a refresh automatically discovers every new row. It updates from the source defined for the PivotTable; if that source is a fixed range such as A1:D500, records added below row 500 may remain outside the report. Converting the source to an Excel table helps because the table expands when data is entered directly beneath it, and a PivotTable based on that table can use the expanded source after refresh.
Before automating, click the PivotTable and inspect PivotTable Analyze > Change Data Source. Confirm that the source points to the intended table, range, connection, or model. Also check for blank header cells, duplicate or changed column headings, and records pasted outside the source table. VBA can issue the refresh command, but it cannot infer that an incorrectly defined source should include unrelated cells elsewhere on the sheet.
Common problems and safe setup
- The macro does not appear in the Macros list: Confirm it is in a standard module, is declared as a public or ordinary
Subwithout arguments, and the workbook is saved as.xlsm. - The open event does not run: Check that the procedure is in the workbook’s
ThisWorkbookmodule, macros are permitted, and Excel opened the macro-enabled file in the desktop app. - New rows are missing: Check the PivotTable source definition and whether new records are inside the source table or range.
- A refresh reports an error: Check the source path, credentials, connection status, and whether the source columns still match the PivotTable fields.
- Refresh is slow: Reduce unnecessary refreshes, avoid firing an event for edits unrelated to the source, and check whether several reports share a cache or whether external queries dominate the time.
Save a separate copy of the workbook before running a macro or changing event code. VBA actions cannot be undone with Ctrl+Z, and a refresh can update report contents or query results in ways that are not reversible through Excel’s undo history. Keep an unmodified backup while validating the source and the result. On Mac, Excel supports VBA, but the Visual Basic Editor shortcut can depend on keyboard settings; try Option+F11 or Fn+Option+F11, or open the editor from the Tools > Macro menu. Ribbon details can differ from Windows. Excel for the web does not execute VBA.
Frequently Asked Questions
How do I refresh a single PivotTable with VBA? In a standard module, declare a PivotTable variable, set it to the report you want, and call its RefreshTable method. For example, use Set pt = Worksheets("Report").PivotTables("PivotTable1") before pt.RefreshTable; use the actual sheet and PivotTable names from your workbook.
Can I refresh all PivotTables with one line? You can call ThisWorkbook.RefreshAll to refresh workbook connections and reports, but it is broader than a loop that targets PivotTables. Prefer the loop when you want explicit control over PivotTables, and Refresh All when associated queries or connections should update too.
Can PivotTables refresh automatically when I change a cell? Yes, a Worksheet_Change event can refresh after direct edits. Limit the event to cells that affect the source and guard against repeated work; formula recalculation alone does not trigger that event.
Why does my PivotTable still show old data after the macro runs? The source might not include the new rows, the connection may not have finished refreshing, or the refresh may have failed. Check Change Data Source, verify the source is accessible, and inspect any error message rather than relying only on the macro running without an obvious pause.
Will the macro run in Excel for the web? No. Excel for the web does not run VBA macros. Use the desktop Excel application to run the code, or use a supported alternative such as a manual refresh or an Office Script where its capabilities suit the workbook.
Related
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Call Another Macro: How VBA Calls WorkYes. In Excel VBA, one macro can run another by calling its Sub procedure by name, such as…
- VBA Find: How to Handle a Failed SearchIn Excel VBA, handle a failed Range.Find search by checking whether its result is Nothing before using it.…
- VBA: Close a UserForm with a Command ButtonTo close an Excel VBA UserForm when someone clicks a command button, put Unload Me in that button’s…
- Range vs Cell in VBA: Differences, Syntax, and ExamplesIn Excel VBA, a cell is represented by a Range object: use Range to refer to an address…
- Copy Paste Values in Excel VBA: Methods and ExamplesTo copy a range’s results—not its formulas—in Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteValues. For a quick…
- Excel VBA: Open a New Workbook with Workbooks.AddTo open a new workbook in Excel VBA, use Workbooks.Add. For example, Set newWb = Workbooks.Add creates and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.