Refresh Worksheet VBA: Methods, Examples, and Fixes

VBA & macros · Updated November 18, 2026

To recalculate formulas on one worksheet with VBA, call that worksheet’s Calculate method. To refresh imported data, use ThisWorkbook.RefreshAll—but that refreshes workbook connections, not only one sheet. The right method depends on whether “refresh” means updating formulas, queries, tables, or PivotTables.

What “refresh” means in Excel

Excel uses refresh and calculation for different jobs. A worksheet can contain formulas that need recalculating, imported records that need retrieving from a source, or PivotTables that need rebuilding from their source data. These operations may affect the same sheet, but one VBA command does not handle all of them.

  • Recalculate formulas: calculate formulas already in the workbook. This does not retrieve new records from a database or query.
  • Refresh a query or connection: request updated data from its source. Depending on the connection and settings, the result may load to a worksheet or the Data Model.
  • Refresh a PivotTable: update its displayed summary from its source or PivotCache. This is not the same as refreshing every workbook connection.

There is no general Worksheet.Refresh method that refreshes everything on one sheet. Instead, VBA exposes methods for particular objects, such as a worksheet’s calculation, a query table’s refresh, or a PivotTable’s refresh. Identifying the object first avoids running a broad refresh when a simple recalculation would do.

Recalculate one worksheet’s formulas

Use a worksheet’s Calculate method when formulas on that sheet need updating. Replace the example sheet name with the worksheet’s tab name. This calculation does not refresh Power Query, external connections, or PivotTables from a changed external source.

Sub RecalculateOneWorksheet()
    Dim ws As Worksheet

    ' Change this to the worksheet tab name.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Calculate
End Sub

For a workbook that has calculation set to Manual, calculating a sheet explicitly can be useful after a macro changes its inputs. To calculate all open workbooks, use Application.Calculate; for a full calculation across open workbooks, Excel also provides Application.CalculateFull. Those wider options may take longer, so prefer the worksheet method when only one sheet needs formula calculation.

In Excel for Microsoft 365 on Windows, Shift+F9 calculates the active worksheet and F9 calculates open workbooks. On some keyboards, including many laptops and Mac keyboards, you may need to hold the Fn key to send F9. These keyboard actions recalculate formulas; they do not refresh external data.

Refresh every workbook connection

When the goal is to update imported data, ThisWorkbook.RefreshAll is the broad, straightforward option. It asks Excel to refresh the workbook’s external data ranges and PivotTable reports. It can therefore affect more than the worksheet the user is viewing. On Windows, the equivalent ribbon command is Data > Refresh All.

Sub RefreshWorkbookData()
    ' Refresh connections and reports in this workbook.
    ThisWorkbook.RefreshAll
End Sub

Refresh operations can run in the background. As a result, the macro may continue to its next line before every query has finished. This matters if subsequent code reads the new rows, copies refreshed values, or saves a final report. A refresh command being issued is not proof that every source has completed successfully.

Excel’s Application.CalculateUntilAsyncQueriesDone can wait for pending asynchronous OLE DB and OLAP queries. Its scope is specific: it is not a universal completion signal for every kind of connection or every Excel task. For a workflow that depends on completion, check the behavior of the particular connection type and configure its refresh behavior appropriately rather than assuming RefreshAll is synchronous.

Refresh data on a particular worksheet

If only one worksheet contains the query table that needs updating, target that object instead of refreshing the entire workbook. The following pattern refreshes query-backed Excel tables on the named sheet and requests a foreground refresh. It skips ordinary tables, which do not have a query to refresh.

Sub RefreshQueryTablesOnOneSheet()
    Dim ws As Worksheet
    Dim lo As ListObject

    ' Change this to the worksheet tab name.
    Set ws = ThisWorkbook.Worksheets("Data")

    For Each lo In ws.ListObjects
        If lo.SourceType = xlSrcQuery Then
            ' Wait for this table's query refresh to finish.
            lo.QueryTable.Refresh BackgroundQuery:=False
        End If
    Next lo
End Sub

This example is intended for query-backed tables that expose a QueryTable. A worksheet may instead show results loaded through a connection or query configuration that is better addressed through the workbook connection. In that situation, refresh the relevant connection by its actual name, or use RefreshAll if the workbook’s setup does not provide a suitable single target. Names vary by workbook, so inspect the connection names in Excel rather than assuming an example name exists.

A legacy query range can also be addressed through the worksheet’s QueryTables collection. The best object to use depends on how the data was created and loaded. For Power Query, Excel’s Queries & Connections pane can help identify the query and its output; a query can be connection-only rather than loaded to a visible table.

Refresh a PivotTable separately

For a PivotTable on a known worksheet, call RefreshTable on that PivotTable. This asks Excel to update the report; it does not necessarily refresh every source connection in the workbook first. If the PivotTable’s source is a query that has changed, refresh the query before refreshing the PivotTable.

Sub RefreshOnePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable

    ' Change the sheet and PivotTable names as needed.
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set pt = ws.PivotTables("PivotTable1")
    pt.RefreshTable
End Sub

PivotTables can share a PivotCache. Refreshing a cache may update other reports that use that same cache, even if the macro names only one PivotTable. If separate refresh behavior matters, check how the PivotTables were created and whether they share a cache. A PivotTable connected to the Data Model or an OLAP source can also have different refresh characteristics from a conventional PivotTable built from a worksheet range.

Choose the narrowest suitable method

Use the operation that matches the source of the stale result. A narrow refresh is usually easier to understand and can avoid unnecessary network requests or delays.

  • Formulas are stale after VBA changes cells: calculate the affected worksheet, or the specific range when appropriate.
  • Power Query or an external data range needs new records: refresh the relevant query or connection; use ThisWorkbook.RefreshAll if a workbook-wide update is intended.
  • A summary report is stale after its source has changed: refresh its source first, then refresh the PivotTable if needed.
  • The workbook has many connections and a refresh macro seems slow: identify which connection supplies the required data, then target it rather than repeatedly refreshing all connections.

In Excel for the web, VBA macros cannot be run or edited in the same way as in the Windows desktop application. Use desktop Excel to run VBA. Excel for Mac supports VBA, but available data connectors and refresh behavior can differ from Windows; check that the specific connection and workbook feature are supported on the Mac version in use. The general distinction between formula calculation and data refresh still applies.

Common problems and safe setup

If a macro appears to do nothing, first confirm that the workbook contains the type of object the code targets. A worksheet name, table name, PivotTable name, and connection name are different identifiers. For example, a visible table called Sales is not necessarily a workbook connection called Sales. An incorrect name can cause a runtime error; a valid name for the wrong object can leave the data unchanged.

Also check whether the workbook is in Manual calculation mode, whether the query requires credentials, and whether the source is reachable. A refresh can fail because of permissions, network access, privacy settings, or source-side errors. Review Excel’s refresh status and any error messages rather than treating a macro finishing without an obvious interruption as confirmation of fresh data.

Before running a macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z, and a macro that refreshes, clears, or replaces data may change the file. If the workbook contains macros, save it in a macro-enabled format such as .xlsm; saving as a macro-free workbook format can remove VBA code. Run only macros from sources you trust, and use Excel’s security prompts and organizational policies to manage macro access.

To add a macro in desktop Excel for Windows, open the Visual Basic Editor, insert a standard module, and paste the code there. Ensure the workbook is saved in a format that supports macros. Test the procedure on a copy and on a small or noncritical workbook first, especially if the refresh connects to production data or triggers downstream steps.

Frequently Asked Questions

How do I refresh only one sheet in VBA?
For formula recalculation, use that worksheet’s Calculate method. For imported data, target the query table or connection that supplies the sheet; there is no universal worksheet-level refresh method for every kind of data source.

Does RefreshAll recalculate formulas?
RefreshAll refreshes external data ranges and PivotTable reports, but it should not be treated as a command to recalculate every formula. Call the relevant worksheet’s Calculate method separately when formula recalculation is required.

Why does VBA continue before the refresh is finished?
Some connections refresh in the background. The macro can move to its next instruction while that work is still running. Use a suitable foreground refresh option where available, or handle completion according to the specific connection type.

Can I refresh a Power Query with VBA?
Yes. You can refresh a query through its workbook connection or use ThisWorkbook.RefreshAll to request a workbook-wide refresh. The correct targeted object and connection name depend on how the query is configured and whether it loads to a table or only to a connection.

Will refreshing one PivotTable refresh all PivotTables?
Not necessarily. A PivotTable can share a PivotCache with other reports, and refreshing shared source data may affect them too. The result depends on how the PivotTables and their caches were built.

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.