Change Source Data VBA

[Big_Dawg]

I have 2 named tables of data, QtrDATA and YTDDATA. I have multiple pivot tables that depict summaries of both data sets. Instead of using 2 seperate for the mutlipe Pivot tables which grows the size of the workbook, I want a macro that will allow me to switch between the various data sources for each pivot. This will will reduce the number of pivots down to a single set of pivots instead of a set for both data sets.
There are about 10 pivot tables for each data set.

If DataSource = QTRDATA in PivotTable, Then Change Data Source to YTD Data for each pivot table.

I need a little help on the code.

 

[xenou]

The data sources are in the same workbook as the pivot tables, correct? How do you want to run the code - from a command button, keyboard shortcut, drop down?

Though I doubt it would work for you an alternative would be one data set with a page field to filter the data at the "top" for year/quarter.

I would think you could get the needed code simply by using the macro recorder as you switch the pivot table source (click the pivot table wizard button, use the back button, and then reselect the data you want to use).

Alex

 

[Big_Dawg]

I would prefer to have a macro that I can just use a shortcut to kick-off to change the data sources.

 

[xenou]

Okay. Try recording your steps as you switch the data source. What code do you get?

If you haven't recorded a macro before, see below under "recording your first macro" (Excel 2003 screenshots)
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

Alex.