How to: Change a Data Source for a Pivot Table

• Apr 01, 2021
• 2 minutes to read

To change a data source for a PivotTable report, use the PivotTable.ChangeDataSource method overloads. You can update your pivot table to reflect changes in the initial source range (e.g., if it was extended by new rows or columns, or existing records or fields were removed), or rebuild the report based on a completely different cell range, as shown in the example below. In the latter case, you should re-create the pivot table and fill it with new data by adding necessary fields to it.

View Example

PivotTableActions.vb

Dim worksheet As Worksheet = workbook.Worksheets("Report1")
workbook.Worksheets.ActiveWorksheet = worksheet

' Access the pivot table by its name in the collection.
Dim pivotTable As PivotTable = worksheet.PivotTables("PivotTable1")
Dim sourceWorksheet As Worksheet = workbook.Worksheets("Data2")
' Change the data source of the pivot table.
pivotTable.ChangeDataSource(sourceWorksheet("A1:H6367"))

' Add the "State" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields("State"))
' Add the "Yearly Earnings" field to the data area.
Dim dataField As PivotDataField = pivotTable.DataFields.Add(pivotTable.Fields("Yearly Earnings"))
' Calculate the average of the "Yearly Earnings" values for each state.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average

PivotTableActions.cs

Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;

// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
Worksheet sourceWorksheet = workbook.Worksheets["Data2"];
// Change the data source of the pivot table.
pivotTable.ChangeDataSource(sourceWorksheet["A1:H6367"]);

// Add the "State" field to the row axis area.
pivotTable.RowFields.Add(pivotTable.Fields["State"]);
// Add the "Yearly Earnings" field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(pivotTable.Fields["Yearly Earnings"]);
// Calculate the average of the "Yearly Earnings" values for each state.
dataField.SummarizeValuesBy = PivotDataConsolidationFunction.Average;