Problem
When a worksheet with a PivotTable is copied in Excel, the PivotTable data source is not updated. If the worksheet is copied within a workbook, then the data sources of the original and copied PivotTables point to the same area. If the worksheet is copied to a new workbook, the data source of the copied PivotTable will point to the area in the original workbook. For example: “[OldWorkbookName.xlsx]OriginalWorksheet!A1:C10″.
ExcelWriter’s CopySheet follows this behavior. If a worksheet that contains a PivotTable is copied with CopySheet, the data source will not be updated.
Solution
Starting in 8.4, ExcelApplication has the ability to change the data source of a PivotTable. Use PivotTable.ChangeDataSource to update the data source of a copied PivotTable.
Example:
PivotTable pt = ws.PivotTables["PivotTable1"];
Area data_area = ws_new_data.CreateArea("A1:G10");
pt.ChangeDataSource(data_area);
Note
PivotTables are sensitive to the column header names in the data source for a PivotTable. If a column header name is changed, the source field associated with that column will get updated, and any PivotTableFields that were created using that source field will be removed from the PivotTable.
This happens in Excel as well.
When changing the data source of the PivotTable, if the new data source is missing any of the columns from the original data source, then fields created from the missing columns will be removed from the PivotTable.
For example, if PivotTable1 has 3 columns: Col1, Col2, and Col3. Changing the data source to an area that has 3 columns: Col1, Col2, and ColC, will cause any fields that were created from Col3 to be removed from the PivotTable.
Follow