如何在数据源工作表新增数据后自动更新多表中的Pivot tables

问题核心是：普通透视表刷新仅会更新现有数据的变动，不会自动扩展数据源范围——你之前的代码只做了刷新操作，但没更新透视表指向的数据源区域，导致新增数据无法被纳入。以下是两种场景下可直接使用的宏代码：

场景1：主表是结构化表格（Ctrl+T 创建的ListObject） #

如果你的source table是Excel结构化表格（这类表格会自动扩展范围），代码更简洁：

Sub RefreshAllPivotsWithNewData()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim sourceTable As ListObject
    
    ' 定位主表的结构化表格（自行修改主表名称和表格名称）
    Set sourceTable = ThisWorkbook.Worksheets("source table").ListObjects("Table1")
    
    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 跳过主表，处理其余工作表
        If ws.Name <> "source table" Then
            ' 遍历当前工作表的所有透视表
            For Each pt In ws.PivotTables
                ' 更新透视表数据源为结构化表格的完整范围
                pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
                    SourceType:=xlDatabase, _
                    SourceData:=sourceTable.DataBodyRange.Address(External:=True) _
                )
                ' 刷新透视表
                pt.RefreshTable
            Next pt
        End If
    Next ws
End Sub

场景2：主表是普通单元格区域 #

如果你的source table是普通单元格区域，需先自动获取最新数据源范围：

Sub RefreshAllPivotsWithNewData()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim sourceWs As Worksheet
    Dim sourceRange As Range
    Dim lastRow As Long
    Dim lastCol As Long
    
    ' 定位主表
    Set sourceWs = ThisWorkbook.Worksheets("source table")
    
    ' 获取主表最新数据源范围（假设表头在第1行，按A列找最后一行，可自行修改关键列）
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row
    lastCol = sourceWs.Cells(1, sourceWs.Columns.Count).End(xlToLeft).Column
    Set sourceRange = sourceWs.Range(sourceWs.Cells(1, 1), sourceWs.Cells(lastRow, lastCol))
    
    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 跳过主表，处理其余工作表
        If ws.Name <> "source table" Then
            ' 遍历当前工作表的所有透视表
            For Each pt In ws.PivotTables
                ' 更新透视表数据源为最新范围
                pt.ChangePivotCache ThisWorkbook.PivotCaches.Create( _
                    SourceType:=xlDatabase, _
                    SourceData:=sourceRange.Address(External:=True) _
                )
                ' 刷新透视表
                pt.RefreshTable
            Next pt
        End If
    Next ws
End Sub

使用说明 #

1. 打开VBA编辑器（Alt+F11），插入新模块后粘贴代码
2. 根据实际情况修改：
   • 主表名称（"source table"）
   • 场景1中的结构化表格名称（"Table1"）
   • 场景2中定位最后一行的关键列（如把"A"改成数据所在列标）
3. 运行宏即可完成所有透视表的数据源更新与刷新

内容的提问来源于stack exchange，提问作者Hassan Samad