' Scans all the Pivot tables contained in the file and refresh the data
Public Sub RefreshPivotTables()
   Dim aWorksheet As Worksheet
   Dim aPivotTable As PivotTable

   ' Find the pivot tables in all worksheets
   For Each aWorksheet In Worksheets
      ' If a worksheet contains more than one pivot table, refresh all of them
      For Each aPivotTable In aWorksheet.PivotTables
         ' Updates also the range of data to be sure that the pivot table uses all the data
         ' If the range of data never changes, it is not necessary to call the sub below.
         RefreshPivotTableRange aPivotTable
         aPivotTable.PivotCache.Refresh
      Next aPivotTable
   Next aWorksheet
End Sub

' Adapts a pivot table data range
Private Sub RefreshPivotTableRange(aPivotTable As PivotTable)
On Error Resume Next
   Dim newRange As Range
   Dim WorksheetName As String

   ' Get the worksheet name
   WorksheetName = Left(aPivotTable.SourceData, InStr(aPivotTable.SourceData, "!") - 1)
   ' The GetDataRangeForWorksheet is a sub that returns the new range of data in the given worksheet.
   Set newRange = GetDataRangeForWorksheet(Worksheets(WorksheetName))
   aPivotTable.SourceData = WorksheetName & "!" & newRange.Address(ReferenceStyle:=xlR1C1)
End Sub