Pivot Table Issues

How to Update a PivotTable Data Source After Duplicating an Excel Sheet

Nimra Malik Oct 9, 2026 868 views

Question details

The user wants to update the data source of a PivotTable to reference the new worksheet automatically after duplicating an existing Excel sheet.

How to Update a PivotTable Data Source After Duplicating an Excel Sheet

Product: Excel
Device & OS: not provided
Scenario: Duplicating a worksheet that contains a PivotTable and needing the copied PivotTable to pull data from the new sheet instead of the original one.
Observed behavior: By default, the copied PivotTable continues to use the original worksheet as its data source and Excel does not offer a formula to change this automatically.

Before you start

Ensure that the newly duplicated worksheet maintains the exact same data structure and column layout (e.g., columns G:P) as the original sheet before attempting to update the PivotTable source.

Solution 1Recommended

Manually Change the PivotTable Data Source

Since Excel does not automatically update the source sheet upon duplication, the most straightforward method for a single sheet is to manually redirect the PivotTable to the new worksheet's data range.

This built-in method is best if you only have a few PivotTables to update. It ensures you select the exact data range required without needing programming skills.

Select the PivotTable

Click on any cell inside the newly duplicated PivotTable to activate the PivotTable Tools on the ribbon.

Open Data Source Settings

Navigate to the 'PivotTable Analyze' (or 'Options' in older versions) tab and click on the 'Change Data Source' button.

Update the Sheet Reference

In the dialog box, modify the sheet name in the reference field to match your new worksheet (for example, change 'Sheet1!$G:$P' to 'Sheet2!$G:$P') and click OK.

Manually Change the PivotTable Data Source

Solution 2

Automate the Update Using a VBA Macro

If you frequently duplicate sheets, you can use a VBA macro to automatically loop through all PivotTables on the active worksheet and update their data source to the current sheet.

Advanced Data Analysis

Manage and Automate PivotTables Seamlessly in WPS Office

WPS Spreadsheet offers powerful PivotTable functionalities and full VBA macro support, allowing you to easily duplicate sheets and manage dynamic data sources just as you would in Microsoft Excel.

1. Open your Workbook: Launch WPS Spreadsheet and open the file containing your PivotTable.
2. Duplicate the Worksheet: Right-click the sheet tab at the bottom, select 'Move or Copy', check 'Create a copy', and click OK.
3. Change the Data Source: Click inside the new PivotTable, navigate to the 'PivotTable Analyze' tab, and click 'Change Data Source'.
4. Apply the New Range: Update the reference to point to the new worksheet's columns and click OK to refresh.

Fully compatible with Microsoft Excel PivotTables and data formats.Supports VBA and macros to automate repetitive data source updates.Lightweight, fast, and free to use for everyday office work.Intuitive UI makes analyzing and summarizing complex data simple.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my PivotTable update automatically when I copy the sheet?

By design, a copied PivotTable retains the exact data source reference of the original PivotTable, which includes the original sheet's name. Excel does not automatically adjust this reference to the new sheet name upon duplication.

Is there an Excel formula to dynamically change the PivotTable data source?

No, Excel does not support using formulas (like INDIRECT) directly inside the PivotTable Data Source field. You must use either the manual 'Change Data Source' option or a VBA macro to achieve this.

Can I update multiple PivotTables on the same sheet at once?

Yes. While you cannot do it manually at once, you can use a VBA macro to loop through all PivotTables on the active worksheet and update their data sources to the new sheet simultaneously.

Excel & Data Problems