Excel: Copying a chart from one file to another

[Cat]

Here's the problem.

I have a bunch of Excel files, possibly up to 12, each with 4 sheets inside them. I have a chart in each file that has the same parameters, pulling from the 4 sheets. I don't want to manually create each chart, since they're all referencing the same sheet titles and cells. I tried copying a chart from one file to another, but that copies the chart + data from the old file, rathering than taking data from the new file.

Any tips? Would a macro help?

 

[mugs]

It doesn't copy any data from the old file, it just references the data in the old file. Right-click the chart, choose Source Data, and the name of the file you copied the chart from will be in brackets before the sheet name. Just delete that and it should work.

Another option would be to make 11 copies of the spreadsheet with the working chart, and copy and paste the data from the other 11 spreadsheets into them.

 

[Cat]

Excellent, thanks!
EDIT: I can't access the Source Data options from the pasted object. I unlocked it, but it's behaving as an embedded object, rather than just a normal chart.

 

[mugs]

Did you try option 2?

 

[Cat]

Yeah, that's basically the macro approach. I was hoping for the more elegant solution, but thank you for your help.