Is there an easy way to update the data source for multiple pivot tables on a single Excel sheet at the same time?
All of the pivot tables reference the same named range, but I need to create a second worksheet that has the same pivot tables, but accessing a different named range.
Ideally I would like to be able to do some kind of search and replace operation (like you can do on formulae), rather than updating each individual pivot table by hand.
>> How to Refresh Pivot Table in Excel Once you have created a Pivot Table, it doesn’t automatically refresh when you add new data or change the existing data. Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself.
- Click on the Insert tab of the ribbon. Click on the down arrow at the bottom of Pivot Table button to open the drop-down list. Click on Pivot Table in the list to open the Create Pivot Table dialog box. By pre-selecting the data range A2 to F12, the Table/Range line in the dialog box should be filled in for us.
- Open your pivot table Excel document. Double-click the Excel document that contains your pivot table. It will open. Go to the spreadsheet page that contains your data. Click the tab that contains your data (e.g., Sheet 2) at the bottom of the Excel window. Add or change your data.
- In the Data group, click on Change Data Source button and select 'Change Data Source' from the popup menu. When the Change PivotTable Data Source window appears, change the Table/Range value to the new data source that you want for your pivot table and then click on the OK button.
Any suggestions?
phreneticphrenetic3 Answers
Excel Pivot Table Dynamic Range
The following VBA code will change the data source of all pivot tables on a single worksheet.
You will need to update the Sheet2 parameter to the name of the sheet with your new pivot tables and the Data2 parameter to your new named range.
Assuming you're willing to use VBA, this might be relevant.
If you iterate through the PivotTable collection on each sheet, you should be able to use the method shown in that post to amend the data source. The syntax should be very similar to use a named range rather than a range of cells.
Ed Harper
Ed HarperHere's a helpful method, adapted from Dynamically Change A Pivot Table's Data Source Range With This VBA Macro Code
Excel Pivot Table Update Data Range Automatically
The PivotTable.SourceData property which is set via the ChangePivotCache method which takes a PivotCache object. To create one, call ActiveWorkbook.PivotCaches.create which takes SourceType and a range as SourceData. Finally, once updated, make sure to call RefreshTable to apply the changes.
Here's what that looks like in code. Just replace Sheet1 with wherever your datasource is located. This will automatically find every pivot table in your workbook and update it.