VBA - change PivotTable source for every PivotTable within worksheet.

d'Holbach

New Member
Joined
Oct 12, 2013
Messages
47
I have a rather complex report which is based on data it references from specifically set out and formatted 9 PivotTables located on a seperate worksheet. The PivotTables pull data from an Excel spreadsheet (specific range) which is located on a network drive. At the moment whenever I want to run the report with new version of data I'm manually updating data sources of all PivotTables to refresh the report by selecing PivotTable and going Change Data Source and selecting a range from external worksheet and repeating this action for all PivotTables.

The connection string I use in the Change Data Source looks like this:
"K:\FolderA\FolderB\FolderC\2013-14\\[nameOfTheFile.xlsx]Data 13-14'!$A$1:$GM$130253"

This is a pseudocode of what I would like to achieve:

Code:
For each PivotTable in Worksheet
                PivotTable.DataSource = "new source"
                PivotTable.Refresh
         Next PivotTable
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Thank you for your reply.

I don't know, how can I find out? All PivotTables should be linked to the same data source (spreadsheet).
 
Upvote 0
For some reason I'm not able to read the connection string, if I go with:

MsgBox ActiveWorkbook.PivotCaches.Item(1).Connection

I get error 1004?
 
Upvote 0
If you're just using a range as the source, you should be looking at the Sourcedata property rather than Connection.
 
Upvote 0
This works for me:

Code:
MsgBox ActiveWorkbook.PivotCaches(1).Connection

If you select the pivot table can you choose Connection Properties if you click the dropdown on Change Source Data in the Data group on the Ribbon's Pivot Table Tools Options tab?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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