Excel：数据源变更时自动刷新同工作表多数据透视表及关联透视图的问题

原VBA代码的问题 #

你这段代码仅完成了刷新现有透视缓存的已有数据，但没有更新缓存的数据源范围边界——主数据源新增的行/列不在缓存初始定义的数据源范围内，所以透视表根本“识别不到”这些新增内容，自然不会在字段列表中显示，关联的透视图也同步不了新字段。

解决思路 #

要让透视表能识别新增字段/行，核心是先让透视缓存的数据源范围动态跟随主数据源扩展，再完成缓存和透视表的刷新。最省心的基础操作是把主数据源转换成Excel表格（Table），因为表格会自动将新增的行/列纳入自身范围。

步骤1：将主数据源转为Excel表格 #

选中主数据源的所有数据区域，按Ctrl+T，勾选“我的表包含标题”后点击确定。给表格设置一个清晰的名称（比如DataSourceTable），后续VBA会用到这个名称。

步骤2：替换并使用新VBA代码 #

这段代码会先更新所有透视缓存的数据源为动态表格，再刷新缓存和关联透视表，确保新增字段能被识别：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pc As PivotCache
    Dim pt As PivotTable
    
    ' 禁用事件触发，避免循环执行
    Application.EnableEvents = False
    
    ' 遍历所有透视缓存，更新数据源并刷新
    For Each pc In ThisWorkbook.PivotCaches
        ' 将缓存数据源指向动态表格（替换成你自己的表格名称）
        pc.SourceData = "DataSourceTable[#All]"
        pc.Refresh
        ' 刷新该缓存关联的所有透视表
        For Each pt In pc.PivotTables
            pt.RefreshTable
        Next pt
    Next pc
    
    ' 恢复事件触发
    Application.EnableEvents = True
End Sub

额外说明 #

• 如果透视表分散在多个工作表中，可将这段代码放到ThisWorkbook的Workbook_SheetChange事件里，触发范围更广。
• 透视图不会自动添加新字段，需要你手动去对应透视表的字段列表中，把新增字段拖入行/列/值区域后，透视图才会同步显示该字段的内容。
• 若不想用表格，也可以用OFFSET函数定义动态数据源名称，但表格的稳定性和易用性更优，推荐优先使用。

内容的提问来源于stack exchange，提问作者Lynette Yeo