Hi, I put together a code from reference online to:

1. use input boxes to input the old source data
2. use input boxes to input the new source data
3. Macro will update all pivot tables which has the old source data with the new source data I input
4. Pivot tables will have the "savedata" option enabled and pivot tables will be refereshed

Option Explicit
Sub ListSourceandChangePivotByOldSource()
'Run Order - 1)lists all pivottable information on the activesheet.
ListPivotsForUpdate
PivotSourceChangeByOldSource

End Sub
Sub PivotSourceChangeByOldSource()
Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable
Dim strSD As Variant
Dim strMsg As String
Dim strMsg1 As String
Dim MySourceData As Variant


'Ensures that if an error happens, the programme will still continue to run
On Error Resume Next

'Turns off Excel default displays so the process faster
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wb = ActiveWorkbook

'Forms string of messages used for later
strMsg = "Enter the Path of the New Source"
strMsg1 = "Enter the Path of the Old Source"

'Sets strpt to the first inputbox and prompts users to select/key in name of the pivot Table
MySourceData = Application.InputBox(Prompt:=strMsg1, Title:="Enter the Name Of Old Source Data", Default:="Old Source", Type:=2)

'Allows user to click on the cell and select it's value
With MySourceData
        .Parent.Parent.Activate
        .Parent.Activate
        .Select
        .Value
End With

' If user clicks Cancel/X button, it prompts the msgbox and closes the sub
If MySourceData = 0 Then
    MsgBox "You clicked the Cancel button"
GoTo err_Handler

Else
' If user types nothing inside, then it prompts the msgbox and closes the sub
If MySourceData = "" Then
MsgBox "You didn't enter an input"

GoTo err_Handler

Else

'If all goes well, it shows the user what he selected/input
MsgBox "Your input was: " & "'" & MySourceData

End If
End If

' Second inputbox asking for input of the Source Data
strSD = Application.InputBox(Prompt:=strMsg, Title:="Enter the Name Of New Source Data", Default:="New Source Data")

'Allows user to click on the cell and select it's value
 With strSD
        .Parent.Parent.Activate
        .Parent.Activate
        .Select
        .Value
    End With
    
' If user clicks Cancel/X button, it prompts the msgbox and closes the sub
If strSD = 0 Then
MsgBox "You clicked the Cancel button"
GoTo err_Handler

' If user types nothing inside, then it prompts the msgbox and closes the sub
Else
If strSD = "" Then
MsgBox "You didn't enter an input"
GoTo err_Handler

Else

'If all goes well, it shows the user what he selected/input
MsgBox "Your input was: " & "'" & strSD

End If
End If

        For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
        
    'condition if old source data matches the one you input, then excel will update
    If pt.SourceData = "'" & MySourceData Then
                
    'updates the pivot table
    pt.ChangePivotCache _
            wb.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="'" & strSD)
    Pt.savedata= true ‘checks the box that saves data making the file bigger
    'refreshes pivot tables
    ‘pt.RefreshTable
 End If

Next pt
Next ws

'prompts this message if all is well
MsgBox "Congrats! Your Pivot Table Data Source Range has been Successfully Updated in This Workbook!", vbInformation
GoTo exit_Handler


'Two scenarios to handle errors and unexpected exits
err_Handler:
  MsgBox "Sorry! We could not Update PivotTable Source Data"
  Resume exit_Handler

exit_Handler:
  Application.EnableEvents = True
  Application.DisplayAlerts = True
  Exit Sub
  
End Sub

I am encountering two problems

1) My workbook consists of multiple pivot tables and some worksheets has the same pivot table referenced. When I enable the option pivot table save data, it makes the file really big. Otherwise, If i manually update each pivottable by clicking on ribbon >analyse >change source data, the file is not as big as when I ran the macro. Attached is the information I extracted
pivot table info.png


2) My workbook consists of multiple slicers and when I use the macro to update all my pivot tables, it does not show in "report connections" on my slicers anymore

slicer.png


Any help would be appreciated! Thank you!