Change pivot table source data via macro

  • Thread starter Thread starter Tim879
  • Start date Start date
T

Tim879

I would like to write a macro to change the source data of a pivot
table. I have all of the other code written, I just cannot figure out
what code I need to get the current pivot table's data source or how
to define a new data source.

Any help would be much appreciated.
 
I wasn't able to find a solution in this code but it is really useful.
Thanks for the tip.

I did find the answer I needed though... the following code worked
For x = 1 To iSheets

'go to a worksheet to change pivot tables
Sheets(x).Activate

'turn warning messages off
Application.DisplayAlerts = False

'change all pivot tables on
'this worksheet one at a time
For Each pt In ActiveSheet.PivotTables
pt.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
strNewPivotTblSrc 'this is defined to be the new
source data for the pivot table
ActiveWorkbook.ShowPivotTableFieldList = False

Next

'turn warning messages on
Application.DisplayAlerts = True
Next
 
Back
Top