VBA Pivot Table Source Data: Set and Update It

VBA & macros · Updated November 18, 2026

In Excel VBA, a PivotTable gets its source from a PivotCache: create the cache with a worksheet range or an Excel table, then use that cache to build the PivotTable. For data that grows over time, an Excel table is usually the easier source to maintain; after adding rows, refresh the PivotTable so it reads the updated records.

What PivotTable source data means

A PivotTable does not normally read and summarize worksheet cells directly every time you interact with it. Excel stores a reference to the source in a PivotCache, which holds the data used by one or more PivotTables. In VBA, the PivotCaches collection creates or exposes those caches, while a PivotTable object represents the report users see.

This distinction matters when you are changing data. Updating cells in the source does not necessarily update the displayed summary immediately. You may need to refresh the cache or PivotTable. Changing the source itself is a separate operation: you can create a cache using a different range or table, then assign that cache to a compatible PivotTable.

For a worksheet-based PivotTable, source data should be a rectangular list with one header row, no blank column headings, and consistent data types within each column. For example, a sales list might have the headings Region, Product, Date, and Sales, followed by one transaction per row. A title, subtotal row, or blank row inside the list can cause Excel to interpret the source incorrectly.

Choose a range or an Excel table

A fixed range is straightforward when the data dimensions never change. It can also be suitable for a one-time report. But if the macro adds new records beyond the last row in the original range, those records are outside the PivotTable’s source. Refreshing a PivotTable does not automatically extend every fixed range to include new rows.

An Excel table is generally a better choice for recurring imports or lists that grow. Tables have names, such as SalesData, and expand as rows are added directly below them or included through table operations. Referencing the table name in VBA avoids calculating the last row yourself and makes the source easier to understand. The source table still needs valid headers and a consistent layout.

  • Use a table when records are routinely appended, the source has a meaningful name, or the workbook will be reused.
  • Use a fixed range when the data is genuinely static or when you intentionally need to exclude cells outside a defined area.
  • Use a dynamically calculated range only when a table is not practical and you can reliably identify the source’s last row and column.

One useful distinction: putting a worksheet range into a cache does not make that range dynamic. If you create the cache from the address of today’s table cells, the cache source may remain that particular address even after the table grows. Passing the table name as the source is intended to preserve the table-based reference; check the resulting PivotTable after changing the source method in a workbook with a different layout.

Create a PivotTable from a named table

This example assumes a worksheet named Data contains an Excel table named SalesData, with columns named Region and Sales. It creates a new PivotTable on a worksheet named Report, starting at cell A3. The destination must be clear, and the PivotTable name must not already be in use on that worksheet.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. The code is for desktop Excel for Microsoft 365 on Windows; Excel for the web cannot run VBA macros.

Sub BuildSalesPivot()
    ' Create a PivotTable from an Excel table.
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim sourceTable As ListObject
    Dim cache As PivotCache
    Dim pivot As PivotTable

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Data")
    Set wsReport = wb.Worksheets("Report")
    Set sourceTable = wsData.ListObjects("SalesData")

    Set cache = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceTable.Name)

    Set pivot = cache.CreatePivotTable( _
        TableDestination:=wsReport.Range("A3"), _
        TableName:="ptSales")

    With pivot
        .PivotFields("Region").Orientation = xlRowField
        .AddDataField .PivotFields("Sales"), "Sum of Sales", xlSum
        .RefreshTable
    End With
End Sub

The macro uses the table’s name when creating the cache, then adds Region as a row field and sums Sales. The names passed to PivotFields must match the source headers. If the intended result is a count, average, or another calculation, choose the appropriate XlConsolidationFunction constant instead of xlSum. A text field cannot be summed meaningfully.

Use a fixed worksheet range

For a fixed source, identify a contiguous range that includes the headers and every record, then create the cache from that range. For example, a list in Data!A1:D500 has a header row in row 1 and records through row 500. In VBA, you can refer to that range directly as wsData.Range("A1:D500"). The exact source address depends on the workbook; do not copy a sample address unless it matches your data.

When the number of records varies, code can calculate the last used row and build a range from the first header cell through that row. The method must match how the data is arranged: finding the last nonblank cell in column A will miss records if column A contains gaps or if the last record has no value there. For a rectangular list, select a reliable key column that is populated on every record, or prefer a table and avoid this calculation.

Do not include totals or notes below the list just because they are on the same worksheet. A PivotTable may treat them as data records. Likewise, a multirow heading or merged header cells are not a substitute for one unique field name per column.

Refresh data versus changing the source

Refreshing tells Excel to read the existing source again; it does not, by itself, point the PivotTable at a different range. In VBA, pivot.RefreshTable refreshes a particular PivotTable. pivot.Cache.Refresh refreshes its cache, and ThisWorkbook.RefreshAll requests a refresh of workbook connections and PivotTables. The last choice can affect more than the one report you are working on.

To change a PivotTable’s source, create a cache for the intended source and use the PivotTable’s ChangePivotCache method where supported. Then refresh and check the result. Source changes can fail or behave differently when the PivotTable is based on an external connection or an OLAP data source rather than a normal worksheet list. A PivotTable cannot always be switched between source types using the same method.

If the source is an Excel table and the cache is already based on that table, appending rows and refreshing is usually the right sequence. If the cache uses a fixed range, first update the cache’s source or rebuild it so the new rows are included; a refresh alone will only reread the old boundaries.

Common source-data errors and fixes

  • “The PivotTable field name is not valid.” Check that every column has a nonblank header, headers are in a single row, and the source range includes that row. Also look for merged cells or a range that starts below the headings.
  • New records do not appear. Confirm that the rows were added inside the table or within the cache’s source boundaries, then refresh. If the source is a fixed range, expand or replace it.
  • VBA reports that a worksheet, table, or field cannot be found. Verify the exact tab name, table name, and header spelling. A table name is not necessarily the same as its worksheet name.
  • The macro cannot create the PivotTable at its destination. Check whether the destination cells overlap another PivotTable or contain content, and whether a PivotTable with the requested name already exists.
  • Values are counted instead of summed, or fields appear in the wrong form. Inspect the source column for text values mixed into numeric data, blank cells, or inconsistent date entries. A refresh does not repair inconsistent source values.

When diagnosing a macro, inspect the source before changing code: confirm the table’s Range, its header row, the number of records, and the PivotTable’s existing cache source. This separates a bad source layout from a naming error or a cache that simply needs refreshing.

Sharing a cache and managing performance

Several PivotTables can share one PivotCache. This can reduce duplicated cache storage and lets reports based on the same source use a common cached dataset. It also means that cache-level changes may affect multiple reports, so check which PivotTables share the cache before changing its source or refresh settings.

For large lists, avoid repeatedly creating new caches inside a loop when one cache can serve several PivotTables. Create the cache once, then create the required reports from it. If a macro runs regularly, consider whether it should refresh one report, a shared cache, or all workbook connections; the broadest refresh is not automatically the best choice.

Excel’s interface offers a useful check after a macro runs: select a PivotTable and use PivotTable Analyze > Change Data Source to review its source, or use Data > Refresh All to refresh workbook data. Ribbon labels can differ slightly by update or platform. Excel for Mac supports VBA in the desktop application, though the Visual Basic Editor and some interface details differ from Windows. Excel for the web can display PivotTables but does not execute VBA.

Frequently Asked Questions

How do I find a PivotTable’s source data in VBA? Inspect its cache using the PivotTable’s PivotCache object and the cache’s SourceData property. The returned source can be a range reference, table name, or other source description. For external and OLAP sources, the source details may be represented differently from a normal worksheet range.

Does refreshing a PivotTable include new rows automatically? Only if its source includes those rows. A PivotTable based on an expanding Excel table can include appended records after a refresh. A PivotTable based on a fixed cell address will not automatically include rows outside that address just because you refreshed it.

Can VBA change a PivotTable’s source range? Yes, for many worksheet-based PivotTables you can create a PivotCache from the new source and use ChangePivotCache. External or OLAP PivotTables have different constraints, so first identify the source type and test changes on a copy.

Should I use a named range or an Excel table? For a growing list, an Excel table is usually simpler because it expands as records are added and has a clear name. A named range can work, but it must be defined so that it includes future rows; a fixed named range has the same boundary problem as any fixed cell range.

Can I run the VBA macro in Excel for the web? No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop Excel application to run VBA; on Mac, use desktop Excel and account for differences in the editor and interface.

Practical rule of thumb

For a repeatable report, organize the source as a clean Excel table, create the PivotCache from the table name, and refresh after loading or editing records. Use a fixed range only when its boundaries are intentional. If new records are missing, first check whether the cache source includes them; if headings fail, inspect the source’s header row; and if changing sources causes errors, verify whether the PivotTable uses a worksheet cache or an external connection.

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.