VBA to Prompt the Change Data Source dialog box of a Pivot Table

[jobe24]

Hello,

I was wondering if there was a way to prompt the "Change Data Source" Dialog box of a specific pivot table found within a file? I have a macro file that rolls the contents for a new reporting period, and I wanted to end it with this prompt so the end user can select the new data source (which is found in another file) without having to manually click on "Change Data Source" to have the dialog box come up.

Is this possible?

 

[Domenic]

You can use the ExecuteMso method of the CommandBars object to execute the Ribbon command for changing the data source. First we need to find the id for the command. So select...

File >> Options >> Customize Ribbon

Then select "All Commands" from the dropdown for "Choose commands from". Then scroll down until you find "Change Data Source", and move your cursor over it until a display pops up. As you'll see, the id for the command is "PivotTableChangeDataSource"...

Now you can use the following line of code to execute the command...

application.CommandBars.ExecuteMso "PivotTableChangeDataSource"

Just make sure that your code first makes the sheet containing your pivottable the active sheet, and that any cell within pivottable is selected.

Hope this helps!