1

I'm trying to collate a number of Excel workbooks into one file.

However, when I copy a sheet from one of the existing workbooks into the new one, the charts I have created refer to the data in the old workbook rather than the new. To illustrate this, here is the reference to the old workbook data after I moved the entire sheet to the new workbook:

=SERIES(,'[Economic activity.xlsx]Gross state product'!$M$3:$R$3,'[Economic activity.xlsx]Gross state product'!$M$13:$R$13,1)

Is there a way to copy charts between workbooks so that the charts point to the new (and identical) data in the new worksheet? I'm unable to use any third-party add-ons for this project so am reliant on solutions that can be used within Excel only.

Improve this question
2
  • There is some discussion of this problem here.
    – Bandersnatch
    Jul 13, 2017 at 3:14
  • Thanks, I'll take a look and see if it answers my question
    – Palaeologus
    Jul 13, 2017 at 4:19

1 Answer 1

Reset to default
2

I've had this problem before too, and it's good to know that there's a simple solution as explained on this Excel help site.

The secret is to copy the sheet to a new workbook using the "Move or Copy Sheet..." command under the Edit menu. This dialog can also be opened by CTRL-clicking on the worksheet tab that you want to copy:

enter image description here

The "To book:" section lists all open workbooks, and you can choose the location of the new sheet in the list. The worksheet will be moved to the new location, or copied if the checkbox is checked.

The charts that are copied refer to data in the new workbook. I've tried this, and it works great! Good luck.

Improve this answer
1
  • Works perfectly - thank you very much!
    – Palaeologus
    Jul 13, 2017 at 22:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .