Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default 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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
change data source of pivot table JohnH Excel Discussion (Misc queries) 9 September 15th 06 12:52 AM
change the ODBC data source of a pivot table JohnH Excel Discussion (Misc queries) 0 August 16th 06 07:10 PM
Q: Change pivot table source path? Mark Excel Discussion (Misc queries) 1 July 27th 06 12:40 AM
Pivot Table external XLS file source change and GETPIVOTDATA refresh mbobro Excel Discussion (Misc queries) 0 July 8th 06 12:45 PM
Pivot Table data source "data source contains no visible tables" Jane Excel Worksheet Functions 0 September 29th 05 08:28 PM


All times are GMT +1. The time now is 03:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"