Updating data source and refreshing all pivot tables through VBA.

Hi all,

I have a spreadsheet containing several pivot tables, all based on the same data source. From time to time, the data source is updated is additional rows (column A never contains any empty cells), therefore I created the following macro to update the data source of all pivot tables as well as refreshing those:

Sub Refresh_All_Pivot_Tables()

Application.ScreenUpdating = False

Dim wb As Workbook
Dim ws As Worksheet
Dim pt As PivotTable

Set wb = ActiveWorkbook
SourceName = Worksheets("MasterSheet").Range("A1:AX1").End(xlDown)

  For Each ws In wb.Worksheets
    For Each pt In ws.PivotTables
      pt.ChangePivotCache _
        wb.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:=SourceName)
    Next pt
  Next ws

ActiveWorkbook.RefreshAll

Application.ScreenUpdating = True

End Sub

However it seems to fail when applying the SourceName variable. I am not sure where the issue could lie, I used F8 and watched the variables but the macro seems to do something completely different than what at I am expecting (i.e. simply update the source of each pivot tables using range A1:AX1 to the bottom of the 'MasterSheet' worksheet).

Any help would be appreciated. Thanks.

PS: I also used

PT.SourceData = SourceName

instead of

wb.PivotCaches.Create(SourceType:=xlDatabase, _
SourceData:=SourceName)

but this did not help.

Try this...

Sub Refresh_All_Pivot_Tables()
    
    Application.ScreenUpdating = False
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim SourceName As Range
    
    Set wb = ActiveWorkbook
    Set SourceName = Worksheets("MasterSheet").Range("A1:AX1").End(xlDown)
    
      For Each ws In wb.Worksheets
        For Each pt In ws.PivotTables
          pt.ChangePivotCache _
            wb.PivotCaches.Create(SourceType:=xlDatabase, _
                  SourceData:=SourceName)
        Next pt
      Next ws
    
    ActiveWorkbook.RefreshAll
    
    Application.ScreenUpdating = True
    
End Sub

I got this to work with your Test5 workbook.

Sub Refresh_All_Pivot_Tables()
    
    Dim Source As String
    Dim pc As PivotCache
    
    Application.ScreenUpdating = False
    
    Source = Worksheets("MasterSheet").Range("A1").CurrentRegion.Address(True, True, xlR1C1, True)
    
    For Each pc In ActiveWorkbook.PivotCaches
        pc.SourceData = Source
    Next
    
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    ActiveWorkbook.RefreshAll
    Application.ScreenUpdating = True
    
End Sub

Originally Posted by Folshot

Could you please upload the version of the spreadsheet you now have? I have cut & pasted the last set of code you provided but still encounter the same issue

Here it is.

Remove the .CurrentRegion property and the 1 from D1

    LastRow = Worksheets("MasterSheet").Range("A" & Rows.Count).End(xlUp).Row
    Source = Worksheets("MasterSheet").Range("A1:D1" & LastRow).CurrentRegion.Address(True, True, xlR1C1, True)

    LastRow = Worksheets("MasterSheet").Range("A" & Rows.Count).End(xlUp).Row
    Source = Worksheets("MasterSheet").Range("A1:D" & LastRow).Address(True, True, xlR1C1, True)