Pitovt Table Datasource selection

Hello!

Right now the pivot tables on tab 2a, is based on tab 2a which sources from Sheet 2. However, if I wanted to change the source to sheet 1, how do I get the pivots tables on tab 2a to update? i have tried the following code, but it reselects the datasource for all pivot tables, making the file too big. Thanks

Sub UpdateAllPivotTableSource()
 Dim WS As Worksheet, PT As PivotTable
 For Each WS In ActiveWorkbook.Sheets
  For Each PT In WS.PivotTables
   PT.SourceData = _
        "'Sheet1"
  Next PT
 Next WS
End Sub

Select allOpen in new window

testing.xls