Pivot Table SourceData Update

[KMacattack]

Is there a way to redefine the Sourcedata for each pivottable using VBA. I am trying to make sure that i can get the excel workbook as small as possible, i have a lot of pivot tables in the workbook.

so i would want something like this:

Sub PT_Update()
Dim sht As Worksheet
Dim PT As PivotTable
For Each sht In ActiveWorkbook.Sheets
     For Each PT In sht.PivotTables
        PT.PivotCache.SourceData = "PivotTable1"
     Next
Next
End Sub

but I am getting an error.
Please help.

Thank you

 

[Derek Brown]

I think that your problem is with the use of "PivotTable1", referring to the pivottable instead of the pivot table's data source.

I would give the sourcedata (the table for the pivot tables) a "name" by highlighting the entire table including the headings, clicking in the Name Box (above Cell A1), typing something like "MyTable" (without spaces) and pressing the Enter key.

The code would then be:

PT.PivotCache.SourceData = "MyTable"