Copy Excel Graphs into new worksheet without graph linking to original worksheet data

Hi,

I want to copy a worksheet containing a graph and the source data in Excel 2007. When I do this, the graph is still linked to the source data on the original worksheet.

In Excel 2003, I was able to copy worksheets and the new worksheet would contain a graph mapped to the source data on the new worksheet (which I could then modify). This provided a quick and easy way of duplicating graphs and changing the data.

Is there a way I can copy the worksheet so that the graph links to the new worksheet data in 2007?

Thanks

I can't answer the immediate question without xl07 in front of me, but as a quick fix, you can use "find and replace" on formulae to remove the reference to the other worksheet without affecting the graph in any other way (i.e. find & replace the filepath & name with nothing).

CC

Hi, Thanks for your reply, but this does not work in Excel 2007.

It doesnt seem to search the Source Data, hence doesnt find the reference to the original worksheet.

Any other suggestions?

Many thanks,

Ben

This feature doesn't change between versions - make sure you're querying formulae, not values (advanced options within find & replace dialogue)

HTH

I have done this.

The source data is in the worksheet 'TS 1 Level 1' and when I search for this, looking in formulas, the data is not found.

Any ideas?

Screenshot attached

Originally Posted by Andy Pope

Find and Replace will not work on the charts Series formula.

You would need code to alter the series formula directly, so something like

Please Login or Register  to view this content.

Hi there,

this is exactly what I need to know. I have generated quite a complicated - at least big - spreadsheet and realised in the end that I couldn't blot my data (problems with blotting time [hh]). So I have copy pasted the graphs from tab "analysis a" to all other analysis-tabs. Unfortunately, all refer to analysis a and replacement using ctrl+H doesn't work.

Does anybody know the solution? I do not know where I should use the code.

Many thanks!