PRB: You Cannot Change the Data Source for a PivotTable in Excel for an Analysis Services Connection

Option Explicit

Sub ChangeServer()

    Dim sh As Worksheet, qy As QueryTable
    Dim pt As PivotTable, pc As PivotCache
    Dim OldPath As String, NewPath As String
    Dim strOld As String, strNew As String

    ' Replace the following paths with the original path or 
    ' server name where your database resided, and the new 
    ' path or server name where your database now resides.
    
    OldPath = "<old server name>"
    NewPath = "<new server name>"

    For Each sh In ActiveWorkbook.Sheets

        For Each pt In sh.PivotTables

            strOld = pt.PivotCache.Connection
            strNew = Replace(strOld, OldPath, NewPath)
            pt.PivotCache.Connection = strNew
            pt.PivotCache.Refresh

        Next pt

    Next sh

End Sub