Change Source Data VBA

Big_Dawg

New Member
Joined
Jan 15, 2008
Messages
27
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.:)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
I would prefer to have a macro that I can just use a shortcut to kick-off to change the data sources.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top