Copying sheets with named range (dynamic) charts

[JoelG]

Hi all,

I'm designing a workbook for dynamically creating charts, where it will select out data using a named range (based on OFFSET, etc).

That all works fine; what I'm trying to work out is what happens when you duplicate the sheet (right clicking on its tab, "Move or Copy..."). What seems to happen is that a duplicate named range is created, pointing to the correct data on the new sheet. However the chart's series have reverted to hard coded values instead of the named ranges.

E.g., for a really simple example, this series:

=SERIES(Sheet1!label1,Sheet1!$A$2:$A$6,Sheet1!$B$2:$B$6,1)

will be changed, when copied, to

=SERIES('Sheet1 (2)'!$A$9,'Sheet1 (2)'!$A$2:$A$6,'Sheet1 (2)'!$B$2:$B$6,1)

I can of course re-type in the copied named range (which has the same name) but that's not very user friendly.

Is there a workaround for this behaviour, or is it something I'll have to deal with through macros? I expect that we're going to want to copy these sheets for quick comparison of multiple charts, etc. I've experimented with both worksheet and workbook scoped ranges.

Any help or tips greatly appreciated - I'll keep googling, but I haven't found anything so far.

Many thanks,
Joel

 

[skiingrudder]

Did you ever figure out a solution to this problem? I am experiencing the same issue. Since a user can copy the sheet on their own, it requires extensive coding to figure out if the user copied a sheet and then to copy the formulas into the new sheet. Thanks.

Jeff

 

[Bill Bisco]

This is a big problem for me as well. Some definitely help is needed.

My problem is closer in that I want to be able to copy a worksheet and the new named ranges all refer to the new worksheet name.