如何通过VBA修改Excel中用命名范围BDATA的所有数据透视表数据源为SDATA

以下是针对需求优化后的VBA代码，仅会修改数据源为命名范围BDATA的数据透视表，将其更新为SDATA，不会影响其他使用不同数据源的透视表：

Sub UpdatePivotSource_FromBDATA_ToSDATA()
' 仅修改数据源为命名范围BDATA的普通数据透视表
' 不适用于OLAP/数据模型透视表
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Const OLD_SOURCE As String = "BDATA"
Const NEW_SOURCE As String = "SDATA"

On Error GoTo err_Handler
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wb = ActiveWorkbook

' 遍历所有工作表及其中的数据透视表
For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
        ' 仅处理数据源为BDATA的透视表
        If pt.PivotCache.SourceData = OLD_SOURCE Then
            pt.ChangePivotCache _
                wb.PivotCaches.Create(SourceType:=xlDatabase, _
                                      SourceData:=NEW_SOURCE)
        End If
    Next pt
Next ws

exit_Handler:
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    MsgBox "已完成更新，仅修改了使用BDATA的透视表"
    Exit Sub

err_Handler:
    MsgBox "更新出错：" & Err.Description
    Resume exit_Handler
End Sub

关键说明： #

• 移除了原代码的交互输入和临时工作表，直接针对明确的数据源进行修改
• 通过Const定义新旧数据源名称，后续调整只需修改常量值
• 核心判断逻辑If pt.PivotCache.SourceData = OLD_SOURCE Then确保只处理目标透视表
• 如果你的BDATA是工作表级命名范围（例如Sheet2!BDATA），只需将OLD_SOURCE改为对应的完整字符串即可
• 保留了环境恢复和错误处理，避免操作过程中干扰Excel正常功能

内容的提问来源于stack exchange，提问作者Lévyprocess