Change pivot table source data via macro
 

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.

Change pivot table source data via macro
 

Try downloading the PivotPlay add-in from Debra Dalgleish's website:
http://www.contextures.com/xlPivotPlay01.html

The vba code in PivotPlay is unprotected.

Post back with questions.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Tim879" wrote:

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.

Change pivot table source data via macro
 

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