Updating data source and refreshing all pivot tables through VBA.

[Folshot]

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.