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
'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
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
For Each ws In wb.Worksheets
For Each pt In ws.PivotTables
Application.EnableEvents = True
Application.DisplayAlerts = True
MsgBox "Could not update pivot table source data"
Any help would be much appreciated.