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:
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: