Copying charts to a new workbook

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:

This entry was posted in Charts, Charts, Excel and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.