我正在尝试将工作簿中使用命名区域BDATA的所有数据透视表的数据源更改为命名区域SDATA。我可以更改工作簿中所有数据透视表的源，但这不是我所需要的，因为我还有其他使用不同命名区域的数据透视表。
这是我使用的代码，它是从这个链接提取：https://www.contextures.com/excelpivottabledatasource.html

Sub PivotSourceChangeAll_Ranges()
'for normal pivot tables only
'not for OLAP-based (e.g. Data Model)
'lists all named ranges
Dim wb As Workbook
Dim ws As Worksheet
Dim wsList As Worksheet
Dim pt As PivotTable
Dim strSD As String
Dim strMsg As String
On Error Resume Next
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wb = ActiveWorkbook
Set wsList = Worksheets.Add

With wsList
.Range("A1").ListNames
.Columns(2).ClearContents
.Columns(1).EntireColumn.AutoFit
End With

strMsg = "Enter one of the Source Data Range Names "
strMsg = strMsg & vbCrLf & "from list shown on worksheet"

strSD = InputBox(Prompt:=strMsg, Title:="Source Data")
If strSD = "" Then
  MsgBox "Cancelled"
  Exit Sub
Else
  For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
      pt.ChangePivotCache _
        wb.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:=strSD)
    Next pt
  Next ws
End If

exit_Handler:
  wsList.Delete
  Application.EnableEvents = True
  Application.DisplayAlerts = True
  Exit Sub
err_Handler:
  MsgBox "Could not update pivot table source data"
  Resume exit_Handler
End Sub

任何帮助都将不胜感激。