Sub Макрос4()
    Dim pt As PivotTable, pc As PivotCache
    Dim dataSheet As Worksheet, ws As Worksheet
    Dim startPoint As Range, dataSource As Range, newRange As String

    ' get worksheet with data
    Set dataSheet = ThisWorkbook.Worksheets("Sheet1")

    ' Dynamically Retrieve Range Address of Data
    Set startPoint = dataSheet.Range("A1")
    Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell))
    newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)

    ' create new PivotCache
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=newRange)

    ' loop through all tables in all sheets
    For Each ws In ActiveWorkbook.Worksheets
     For Each pt In ws.PivotTables

      ' update pivot source and refresh
      pt.ChangePivotCache pc
      pt.RefreshTable

     Next pt
    Next ws
End Sub