VBA Change Pivot Table Source: Code and Tips

VBA & macros · Updated November 18, 2026

To change a PivotTable’s source with VBA, create a PivotCache for the new worksheet range or Excel table, assign it with PivotTable.ChangePivotCache, and refresh the PivotTable. The examples below show both approaches for Excel for Microsoft 365 on Windows; save a copy of the workbook first, because macros cannot be undone with Ctrl+Z.

What changing a PivotTable source actually does

A PivotTable displays a summary built from a data source held in a PivotCache. If the source range has moved, grown, or been replaced, refreshing the existing PivotTable may not be enough: refresh reads from the cache’s current source. To point the PivotTable somewhere else, VBA generally creates a cache using the new source and assigns that cache to the PivotTable.

The key object-model steps are:

  • Identify the worksheet range or Excel table that contains the source data.
  • Create a workbook PivotCache with Workbook.PivotCaches.Create.
  • Use PivotTable.ChangePivotCache to assign it.
  • Refresh the PivotTable so the displayed results reflect the new data.

This changes the source, not the PivotTable’s design. Existing row, column, filter, and value fields are usually retained if the new data has matching field names. If the new source uses different column headings, fields may be missing or the layout may need repair.

Prepare the source before running a macro

Make the source a simple rectangular dataset: one header row, one record per row, and one field per column. Avoid blank header cells, merged headings, and completely blank rows or columns inside the data. PivotTables use the header names as field names, so a missing or ambiguous heading can prevent a cache from being created correctly.

Before you run the macro, confirm these details:

  • The workbook and worksheet names in the code match the file.
  • The PivotTable name is correct. Select the PivotTable and check its name on the PivotTable Analyze tab, in the PivotTable Name box.
  • The new data includes the same field names if you want to preserve the existing layout.
  • The workbook is saved as a macro-enabled file, such as .xlsm, if you need to retain the code.

For Windows, open the VBA editor with Alt+F11, choose Insert > Module, and paste in the macro. You may need to enable the Developer tab or allow macros under your organization’s security policy. Excel for the web can open a workbook containing VBA, but it cannot run VBA macros. Excel for Mac supports VBA, though the editor shortcut can vary with the keyboard; use Developer > Visual Basic if the shortcut differs.

VBA example: change the source to a worksheet range

This example uses the connected data region beginning at cell A1 on a worksheet named Data. It assigns that region to a PivotTable named PivotTable1 on a worksheet named Report. Change those names to match your workbook. The external R1C1 address identifies the source worksheet and workbook as well as its boundaries.

Sub ChangePivotSourceToCurrentRegion()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim sourceRange As Range
    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim col As Long

    Set wb = ThisWorkbook
    Set wsData = wb.Worksheets("Data")
    Set wsReport = wb.Worksheets("Report")
    Set pt = wsReport.PivotTables("PivotTable1")

    ' Use the contiguous data block starting at A1.
    Set sourceRange = wsData.Range("A1").CurrentRegion

    ' A PivotTable needs a header row and at least one data row.
    If sourceRange.Rows.Count < 2 Then
        Err.Raise vbObjectError + 1000, , "Source needs a header and data row."
    End If

    ' Check that every source column has a heading.
    For col = 1 To sourceRange.Columns.Count
        If Len(Trim$(CStr(sourceRange.Cells(1, col).Value))) = 0 Then
            Err.Raise vbObjectError + 1001, , "A source header is blank."
        End If
    Next col

    ' Build a cache from the new range, then assign and refresh it.
    Set pc = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceRange.Address( _
            ReferenceStyle:=xlR1C1, External:=True))
    pt.ChangePivotCache pc
    pt.RefreshTable
End Sub

CurrentRegion is convenient when the source is a continuous block with no fully blank row or column dividing it. It can include unwanted adjacent content, or stop early if a blank row separates records. If your data does not have a dependable boundary, replace the CurrentRegion line with an explicitly chosen range, such as wsData.Range("A1:F5000"), or use an Excel table as shown next.

VBA example: use an Excel table as the source

An Excel table is often the better source for data that grows over time. New rows added to the table become part of the table automatically. The PivotTable still needs a refresh to display those rows, but you do not have to calculate a new last row or edit a fixed address each month.

Format the source as a table with Insert > Table, then give it a clear name on the Table Design tab, such as SalesData. In the following macro, the table is on Data and the PivotTable is on Report:

Sub ChangePivotSourceToTable()
    Dim wb As Workbook
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim sourceTable As ListObject
    Dim pt As PivotTable
    Dim pc As PivotCache

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

    ' Create a cache from the table name.
    Set pc = wb.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=sourceTable.Name)
    pt.ChangePivotCache pc
    pt.RefreshTable
End Sub

The table name is not necessarily the same as the worksheet name. Check it under Table Design > Table Name. If your Excel setup or source arrangement does not accept the table name as the cache source, use the table’s range address with sourceTable.Range.Address(ReferenceStyle:=xlR1C1, External:=True); however, an address represents the current size rather than the table’s continuing identity. Keep the refresh step either way.

Changing one PivotTable or several

The examples change one named PivotTable. For several PivotTables, repeat the cache assignment for each one that should use the new source. You can create the cache once and reuse the pc variable in a loop, provided the PivotTables belong to the same workbook and the new source is appropriate for all of them. Check field names before changing multiple reports: a source with different headings may leave some PivotTables with unavailable fields.

PivotTables can share a cache. Changing one PivotTable to a newly created cache does not mean every other PivotTable in the workbook should be assumed to have moved to the new source. Explicitly identify which reports need the change, then verify each one. If preserving shared-cache behavior matters for workbook size or coordinated filtering, review the cache relationships after the change rather than creating separate caches without a reason.

Refresh, save, and verify the result

ChangePivotCache assigns a cache; RefreshTable asks the PivotTable to update its displayed results. A successful macro run is not proof that the summary is correct, so inspect the result and the source boundaries. For example, check that the final data row appears in the PivotTable and that its grand total agrees with the underlying records.

You can also refresh all PivotTables in a workbook when that is the intended action, but avoid doing so automatically if the workbook contains unrelated reports or slow external connections. A manual alternative in Excel for Microsoft 365 on Windows is to select the PivotTable and choose PivotTable Analyze > Refresh. To refresh every PivotTable, use Data > Refresh All. These refresh commands do not by themselves change a fixed source range into a larger one.

After confirming the output, save the workbook. If the macro changes a report used by other people, keep a backup or test the procedure on a copy first. VBA changes cannot be reversed with Ctrl+Z, and a macro can update workbook content before a later line encounters an error.

Common problems and how to diagnose them

  • Subscript out of range: A worksheet, table, or PivotTable name in the code does not match the workbook. Check spelling, spaces, and the PivotTable name box.
  • New rows do not appear: A fixed range may still end above the new records. Expand the range or use an Excel table, then refresh.
  • Fields disappear or the layout changes: The replacement data has different headers. Match the old field names where possible, or update the PivotTable fields intentionally.
  • Blank or duplicate headings cause trouble: Give every column a nonblank, distinct heading. A source must have usable field names.
  • The PivotTable uses an external or Data Model source: The examples are for worksheet data represented by an xlDatabase cache. OLAP, Power Pivot/Data Model, and some external connection PivotTables use different connection mechanisms; ChangePivotCache is not a universal way to retarget them.
  • The macro is blocked: Excel’s macro settings or an organization’s security controls may prevent execution. Do not enable macros from an untrusted workbook just to run a source-change procedure.

When to use the ribbon instead of VBA

For a one-time edit, the ribbon may be simpler. Select the PivotTable and choose PivotTable Analyze > Change Data Source, then select the new table or range and confirm. This is useful when you want to inspect the chosen range visually or do not need to repeat the task.

VBA is more useful when a recurring process imports or replaces source data, updates several reports, or must run as part of a larger macro. If the source grows regularly, a table plus a refresh may be easier to maintain than repeatedly calculating and assigning a new range. Neither approach removes the need to check that the PivotTable is summarizing the intended records.

Frequently Asked Questions

Can I change a PivotTable source without rebuilding the PivotTable?
Usually, yes. Create a cache for the new worksheet source, assign it with ChangePivotCache, and refresh. The existing layout can remain if the replacement source contains compatible field names.

Does refreshing a PivotTable automatically include new rows?
Only if its source includes those rows. A fixed address does not necessarily expand when records are appended. An Excel table expands as rows are added, but the PivotTable still needs to be refreshed.

Can I change the source for a PivotTable connected to the Data Model?
Not with the worksheet-range examples here. Data Model and OLAP PivotTables use connection-based sources, so their source needs to be changed through the relevant model or connection workflow.

Will changing one PivotTable’s source change every PivotTable?
Do not assume that it will. Some PivotTables share a cache, but assigning a newly created cache to one PivotTable is not a reliable substitute for deliberately updating and checking every report that should change.

Can I run this macro in Excel for the web?
No. Excel for the web can preserve a workbook that contains VBA, but it does not run VBA macros. Use the desktop Excel app to execute the code.

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.