Pivot Table Not Refreshing Through VBA? Causes and Fixes

VBA & macros · Updated November 18, 2026

If an Excel PivotTable does not update through VBA, first confirm that its source includes the changed rows, then refresh the correct PivotTable or connection after the source data has finished loading. For a standard PivotTable, the essential call is pt.RefreshTable; the code below shows how to apply it to every PivotTable in the workbook and report an error if a refresh fails.

Start with the refresh method and target workbook

For one PivotTable, the VBA pattern is to assign the worksheet and PivotTable to variables, then call its RefreshTable method. For a workbook-wide refresh, loop through worksheets and their PivotTables rather than assuming that refreshing the currently selected report will refresh every report. PivotTables can sit on different sheets, and a macro that targets the wrong workbook or sheet can run without changing the report you are looking at.

This example refreshes each PivotTable in the workbook that contains the macro. Save a copy of the workbook before running a macro: VBA actions cannot be undone with Ctrl+Z.

Sub RefreshAllPivotTables()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim oldScreenUpdating As Boolean
    Dim errorMessage As String

    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo HandleError
    Application.ScreenUpdating = False

    ' Refresh each PivotTable in the workbook containing this code.
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws

    GoTo CleanUp

HandleError:
    errorMessage = "Refresh stopped: " & Err.Description

CleanUp:
    Application.ScreenUpdating = oldScreenUpdating
    If Len(errorMessage) > 0 Then MsgBox errorMessage, vbExclamation
End Sub

Put the procedure in a standard VBA module, then run it with Developer > Macros, select RefreshAllPivotTables, and choose Run. On Windows, Alt+F11 opens the Visual Basic Editor. In Excel for Mac, the Visual Basic Editor is available from Tools > Macro > Visual Basic Editor; ribbon and menu details can vary by release. If the code is stored in a separate file such as PERSONAL.XLSB, ThisWorkbook means the file holding the code, not necessarily the workbook with the report. In that situation, explicitly assign the intended workbook rather than silently relying on the active window.

What a PivotTable refresh actually does

A PivotTable displays a summarized view of source records. Refreshing makes Excel reread its source and update the report’s values, labels, and items. The report does not necessarily scan every cell in the workbook or infer which data you intended to add. Its source may be a cell range, an Excel Table, an external connection, or the Data Model, and each source type affects what must happen before the PivotTable can show new results.

PivotTable.RefreshTable refreshes that report from its source. A PivotTable also uses a PivotCache, the stored data structure behind the report; several PivotTables can share one cache. This is why repeated refreshes may be redundant in a workbook with many reports, although looping through all reports is a straightforward way to make sure each is asked to refresh. For a basic report, refreshing the PivotTable is usually the right first test. Refreshing a PivotCache directly is not a substitute for updating an external source or waiting for a query that has not finished.

It also helps to distinguish a refresh from recalculation. Pressing F9 recalculates formulas, but it does not generally refresh a PivotTable’s source data. Likewise, changing a cell’s value may recalculate dependent formulas while leaving the PivotTable’s displayed summary unchanged until a refresh is requested.

Check whether the source range contains the new data

A refresh can succeed and still show no new records if the PivotTable’s source does not include them. For example, suppose the source is A1:D100 and VBA adds a record in row 101. Refreshing the report will not necessarily include that row because the defined source ends at row 100. In Excel, select the PivotTable and use PivotTable Analyze > Change Data Source to inspect the source reference.

For data that grows over time, format the source as an Excel Table using Insert > Table. A PivotTable based on a Table can use its expanding set of rows as records are added to that Table. If VBA writes data below the Table but does not extend the Table, those rows still are not part of its source. Check the Table’s name and resize it when your import process writes outside its current boundaries.

Other source-range checks worth making include:

  • Confirm that the macro writes values into the expected sheet and columns before refreshing.
  • Check for blank header cells, duplicate field names, or an unexpected change in the header row.
  • Look for formulas that have not calculated yet if the source contains formula results that determine the records or values.
  • Use Change Data Source to check that a PivotTable copied from another workbook has not retained an old file or range reference.

A useful diagnostic is to select the source directly and verify that the new record is physically inside the range or Table named by the PivotTable. If it is not, changing the VBA refresh call will not solve the problem.

Wait for queries and connections to finish

When the source is an external database, text file, or Power Query result, the refresh sequence matters. A macro can start a connection refresh and then ask the PivotTable to update before the incoming data has arrived. The PivotTable may then display the previous data, even though the macro ran without an obvious error. This is especially likely when a connection is configured to refresh in the background.

In a workbook that uses external data, identify what supplies the PivotTable before choosing the order of operations. Refresh the relevant connection or query, wait for its completion where the connection supports background refresh, and then refresh the PivotTable. Workbook-wide ThisWorkbook.RefreshAll asks Excel to refresh external data ranges and PivotTable reports, but it may return while background work is still underway. It is not a guarantee that every asynchronous connection has completed before the next VBA statement runs.

For a suitable QueryTable connection, VBA can request a non-background refresh using its Refresh method with BackgroundQuery:=False. That option is specific to the object and connection involved; it is not a universal switch that makes all Power Query, Data Model, or external operations synchronous. Check the connection type and its properties, and avoid adding arbitrary delays such as Application.Wait as a supposed fix. A fixed pause can be too short on a slow connection and unnecessarily long on a fast one.

If the PivotTable is based on the Data Model or an OLAP connection, refresh the underlying model or connection as appropriate, not just a worksheet range. These reports have connection and model behavior that differs from a PivotTable based on a normal range. If the source query itself fails, troubleshoot its credentials, file path, permissions, or query steps before changing the PivotTable code.

Match the symptom to the likely cause

The exact symptom often narrows the diagnosis. A macro that raises an error points to a different problem from one that completes but leaves old values visible. Check the VBA editor’s highlighted line and the error message, then test the same report manually using PivotTable Analyze > Refresh. If manual refresh also fails, the issue is more likely to be the source, connection, or PivotTable configuration than the loop itself.

  • New rows are missing: inspect the source range or Table boundaries. A successful refresh cannot add records that are outside the source.
  • Old values remain after an import: check whether the import has finished and whether the macro is refreshing the same workbook and PivotTable shown on screen.
  • Only one report changes: the procedure may target one PivotTable, or the other reports may use different caches, sources, or connections.
  • The macro reports an object or subscript error: verify worksheet and PivotTable names, spelling, and workbook references. Names are not interchangeable with captions shown in the report.
  • The report refreshes manually but not from the macro: check whether events, workbook selection, protection, or an earlier VBA error is interrupting the procedure.
  • Some fields or items behave unexpectedly: inspect filters, grouping, calculated fields, and the source headers. A refresh does not remove every deliberate report setting or repair a changed source schema.

For a named PivotTable, inspect its name in the PivotTable Analyze tab’s PivotTable Name box, then check the worksheet tab name used by VBA. Sheet tab captions are not the same as a sheet’s VBA code name. A loop over ws.PivotTables, as shown above, avoids hard-coding individual report names when the goal is to refresh every PivotTable.

Check macro execution, protection, and workbook settings

Make sure the procedure actually runs. The workbook must be saved in a macro-enabled format such as .xlsm if it contains VBA, and macros must be permitted by the organization’s security settings. Do not lower security settings broadly to troubleshoot one file; use a trusted file location or follow the organization’s approved process. If the macro is assigned to a button, confirm that the button still points to the correct procedure.

Worksheet protection can also matter, particularly if a macro changes filters, layout, or other report properties in addition to refreshing. Test with a copy and check whether the sheet is protected and whether the PivotTable itself allows the action. A refresh may also be blocked by a broken source path, unavailable network location, expired credentials, or an open prompt hidden behind another window.

To refresh a PivotTable when the file opens without VBA, select it and use PivotTable Analyze > Options, open the Data tab in the PivotTable Options dialog, and select Refresh data when opening the file. This setting is useful for ordinary opening behavior, but it does not ensure that a separate import macro has finished before the report refreshes. When VBA controls the workflow, explicitly order the import, source completion, and PivotTable refresh steps.

Choose between a targeted refresh and RefreshAll

Use a targeted call such as pt.RefreshTable when only one known PivotTable needs updating and its source is already ready. This makes the intended action clear and avoids requesting refreshes for unrelated connections. Use ThisWorkbook.RefreshAll when the task is genuinely to refresh the workbook’s external data and PivotTable reports together, but account for background operations that may continue after the call returns.

For a batch of PivotTables, the loop in the first section is easier to audit than a long list of hard-coded calls. However, if a workbook has many reports sharing caches or has slow external connections, repeated calls can add time. Profile the actual workflow and refresh the required source or shared cache deliberately; do not assume that calling refresh many times makes the data more current. A dependable sequence is usually: update the source, wait for its refresh to complete, then refresh the report or reports that depend on it.

Frequently Asked Questions

Why does VBA run without an error but the PivotTable stays unchanged?
It may be refreshing a different workbook or report, or the source may not include the changed records. For external data, the query may also still be running. Confirm the source and target, then try a manual refresh to separate a VBA targeting problem from a data-source problem.

Should I use RefreshTable or RefreshAll?
Use RefreshTable for a specific PivotTable whose source is ready. Use RefreshAll when you want Excel to request refreshes for workbook data sources and PivotTable reports. Because background refresh can continue after RefreshAll returns, it may not be the right final step when later code requires the new data immediately.

Does refreshing update rows added below the original range?
Not if those rows are outside the PivotTable’s source. Check PivotTable Analyze > Change Data Source, or use an Excel Table as a growing source and make sure the new records are actually added to that Table.

Why does F9 not refresh a PivotTable?
F9 recalculates formulas; it is not the normal command for rereading PivotTable source data. Use PivotTable Analyze > Refresh, or call the PivotTable’s RefreshTable method in VBA.

Can a macro refresh a PivotTable before a Power Query finishes?
Yes. If the query runs in the background, code that immediately refreshes the PivotTable can run ahead of the query. Refresh the relevant source first and use a completion method appropriate to that connection before updating the report.

Why does the macro work in one file but not another?
Workbook references, sheet names, PivotTable names, source types, protection, and connection settings can differ. In particular, ThisWorkbook refers to the workbook containing the code, which may not be the report workbook when the macro is stored elsewhere.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.