copying graphs from one worksheet to another

[msampson]

I am so glad I found this forum. Now I can find out about all the little anoying things!
I will frequently create a workbook where I will set up all sorts of calcuations and graphs on the first sheet and then copy it all to the next sheet where I use a different data set to do the same things. When I copy a graph over though it always has a data source that points to sheet 1. Is there any way to copy it over so it becomes a more generic format and points to the same cells but the new sheet (or no sheet at all)?

Thanks for your help about the curve integration. With a little tweaking I was able to use the macro you pointed out.

Maureen

 

[Andrew Poulsom]

Unfortunately all references in the SERIES formula for a Chart are absolute, so there is no easy solution.

You could copy the entire sheet then overwrite the data by copy/paste from the sheet you want to chart.

 

[msampson]

Yes, that's what I do, copy the entire sheet, paste in the new data range and then I have to go into the chart and change the SERIES to point to this sheet instead of the previous one. I was just hoping there was a way to copy the graph without the absolute reference in it so that it would automatically update when I put overwrite the data on the new sheet.

 

[Andrew Poulsom]

When I copy the entire sheet (Edit|Move or Copy Sheet) the SERIES formulas refer to the new sheet. Doesn't that happen with you?

 

[msampson]

I usually just click in the top left corner to hightlight everthing and then say copy and then paste on the next sheet. Then the graph on sheet 2 is graphing data from sheet 1.

 

[Andrew Poulsom]

That's copying all the cells, not copying the sheet. Right click the sheet tab, choose Move or Copy, check Create a Copy and click OK. That will create a new sheet and the SERIES formula will refer to it.