Copy graph to new workbook while leaving data in original workbook

[smjpl]

Hi folks,

How can I copy the graphs to the new workbook but have the graphs still linked to there original workbook? The same way when MS Word does it (Maybe 2010 only). The graphs is in word but is still linked to its original excel workbook. You can still change chart types and all that when it is in MS Word.

I am using excel to monitor energy usage. I am trying to keep file sizes low by making a new workbook each week. Each week has graphs of total power, generated power etc. I want to compare each week in a new separate workbook. When I copy the graphs to the new workbook, all the data in the graphs disappears. I go into "Select Data" and look at the source.

This is what it reads: "='[Energy Monitoring Week 1 (22-10-12).xlsx]Results'!$F$2:$F$9768". Once I hit ok it says "reference is not valid. Reference must be to an open workbook".

Any ideas on my problem. Normally like most of my questions there is an easy fix and I am just doing something stupid.

Just to be clear, I don't want to copy the original workbook to the new one. The data sets are too large and this is half the reason that I am separating them into weekly segments in the first place.

Cheers

[FDibbins]

use cut/paste, instead of copy/paste, it will take the links with it

[smjpl]

Thought I should also say. Once I get to graphs into new work book, i.e. week 1 & week 2 I want to be able to copy one to the other and overlay the two weeks on top of each other.

Just thought I should make that clear.

[smjpl]

use cut/paste, instead of copy/paste, it will take the links with it

That didn't work. I got the same error as when I copy/paste it. This is starting to make me think there is something wrong with my settings.
Cheers for the input though.

[MrShorty]

Once I hit ok it says "reference is not valid. Reference must be to an open workbook".

Can I therefore assume that, at this point in the process anyway, the referenced workbook is closed? The obvious solution is to open the source workbook.

I frequently link a chart in one workbook to data in another workbook, and have often come across this error. Linking across workbooks like this can work, but it seems that you have to get used to having the source workbook open whenever you need to edit the data series for the chart. Once I have the chart done, then I can close the source workbook(s) and the chart should stay. Note that I work exclusively with scatter plots, so it is possible that other chart types will not work as well.

Sometimes I find it easier to make a copy of the desired data in the workbook containing the chart. The copy can even be linked to the external source if desired. Then build the chart off of the copied data.