Change Data Source Range for ALL Pivot Tables that are Using a Particular Named Range Within Excel via VBA

Question

I'm attempting to switch the named range BDATA used by all of the pivot tables in a workbook to the named range SDATA. All of the pivot tables in the workbook's source could be changed, however, this isn't what I need because other pivot tables I have different named ranges.

This is the code that I used, which was extracted from this link: 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

Any help would be much appreciated.

Answer 1

Try this:

Sub Tester()
    Dim wb As Workbook, ws As Worksheet, pt As PivotTable, pc As PivotCache
    Dim src As String
    
    Set wb = ThisWorkbook

    For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
            src = pt.PivotCache.SourceData
            If src = "BDATA" Then
                pt.PivotCache.SourceData = "SDATA"
                Debug.Print "replaced source"
            End If
        Next pt
    Next ws
End Sub