Replace pivot table source paths with current path.

FixPivotTableSources.bas

Attribute VB_Name = "Module1"
Public Sub Update_Pivot_Tables()
On Error GoTo Exception

    Dim sheet As Worksheet, table As PivotTable
   
    For Each sheet In ThisWorkbook.Sheets
        For Each table In sheet.PivotTables
            Dim newSource As String, sourceType As XlPivotTableSourceType, version As Variant, i As Long
            i = InStr(1, StrReverse(table.SourceData), "\", vbTextCompare)
            If i <= 0 Then
                newSource = table.SourceData
            Else
                newSource = "'" & ThisWorkbook.Path & "\" & Right(table.SourceData, i - 1)
            End If
            sourceType = ThisWorkbook.PivotCaches(table.CacheIndex).sourceType
            version = ThisWorkbook.PivotCaches(table.CacheIndex).version
            Debug.Print table.SourceData & " -> " & newSource
            sheet.PivotTables(table.Name).ChangePivotCache ThisWorkbook.PivotCaches.Create(xlDatabase, newSource, version)
        Next
    Next
    Debug.Print "Done!"
    
    Exit Sub
Exception:
    Dim Result As Variant
    Result = MsgBox("Error: " & Err.Number & vbCrLf & vbCrLf & Err.Description, vbCritical Or vbOKCancel)
    Select Case Result
        Case vbOK
            Resume Next
        Case vbCancel
            Debug.Print "Cancelled."
            Exit Sub
    End Select
End Sub