When an Excel worksheet including a chart(s) is copied to a new workbook the chart still links to the data ranges in the original workbook. Over the years I have spent a fair bit of time editing the chart ranges to their intended location in the new workbook, but recently I decided to check if there is a better way, and found:

Copy Chart to New Sheet and Link to Data on New Sheet at Jon Peltier’s blog

The procedure is:

• Right click on the tab of the worksheet to be copied
• Select “Move or Copy …” then in the dialog box select “New book” under “To book”, and select the “Create a copy” check box
• Click OK
• The worksheet will be copied to a new file (called book1.xlsx), including any charts, with the charts linking to the data in the new file.
• Save the new file with a new name, remembering to save as .xlsb or .xlsm if you want to add any VBA code.

Note that the worksheet may also be copied to an existing file, or to a new position in its current file. Also it is possible to copy more than 1 worksheet by selecting the sheets to be copied before right-clicking on one of the selected tabs.

Copying the worksheet:

The resulting new workbook, with chart linked to the data in the new file: