Updating data source on multiple pivot tables within Excel
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.
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.
Sub Change_Pivot_Source() Dim pt As PivotTable For Each pt In ActiveWorkbook.Worksheets("Sheet2").PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="Data2") Next pt End Sub
Updating data source on multiple pivot tables within Excel, 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 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.
How to Dynamically Update All Pivot Tables Data Source in Excel, If the source data and pivot tables are in different sheets, we will write the VBA code to change pivot table data source in the sheet object that contains the source� Change the source data for a PivotTable. Click the PivotTable report. On the Analyze tab, in the Data group, click Change Data Source , and then click Change Data Source . The Change PivotTable Data Source dialog box Do one of the following: To change the data source of a PivotTable to a
I combined both the above codes and now with the below code you can use either table or range reference for your source data. All you have to do is put in your source data by replacing the
* sign and you're good to go.
Sub Change_Pivot_Source() Dim pt As PivotTable Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:="*****") Next pt Next ws End Sub
Change Data Source One Pivot Table. Select a cell in the pivot table that you want to change. On the Ribbon, under PivotTable Tools, click the Options tab. Click the upper part of the Change Data Source command.
PivotTable.SourceDataproperty can be set which is set via the
- To create a new
- You'll need to pass in a
- Finally, once updated, make sure to call
RefreshTableto apply the changes.
Here's an example that will automatically find every pivot table in your workbook and update it.
Sub AdjustPivotDataRange() Dim pt As PivotTable, pc As PivotCache Dim dataSheet As Worksheet, ws As Worksheet Dim startPoint As Range, dataSource As Range, newRange As String ' get worksheet with data Set dataSheet = ThisWorkbook.Worksheets("Sheet1") ' Dynamically Retrieve Range Address of Data Set startPoint = dataSheet.Range("A1") Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell)) newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1) ' create new PivotCache Set pc = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=newRange) ' loop through all tables in all sheets For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables ' update pivot source and refresh pt.ChangePivotCache pc pt.RefreshTable Next pt Next ws End Sub
"Sheet1" with wherever your datasource is located.
I would like to update the data source of multiple pivot tables. I can't use tables so I would like to use visual basic. The data source is going to be the same for all pivot tables and I can either change it in VB or if it's possible to get the pop-up box asking for the source that's even better. Excel 2013. Thanks in advance!
stNewSource = Sheets ("Source Data").Range ("A1").CurrentRegion.Address (True, True, xlR1C1) PC.SourceData = "'SOURCE DATA'!" & stNewSource. End If. Next. ' refresh all pivot tables. For Each WS In ActiveWorkbook.Worksheets. For Each PT In WS.PivotTables. PT.RefreshTable.
In order to change the source data for your Pivot Table, you can follow these steps: Add your new data to the existing data table. In our case, we'll simply paste the additional rows of data into the existing sales data table. Here's a shot of some of our additional data.
Update Pivot Tables Automatically. 1. Open the Visual Basic Editor. You can do this by clicking the Visual Basic button on the Developer tab of the ribbon. The keyboard shortcut for 2. Open the Sheet Module that contains your source data. 3. Add a new event for worksheet changes. 4. Add the VBA
- Worked well, Thank you
- @kilemit: I just made the code consistent. Either use twice activeworkbook or twice thisworkbook, but the proposed mix is a guaranteed source of trouble. It will only work if the activeworkbook is thisworkbook.