Copying a sheet with a chart that uses Excel Names

[Holger Gerths]

Hi Group,

who knows the following XL-2003 effect and its solution:

An Excel sheet with a chart that uses Excel-names in the data-source. (e.g.
=Sheet1!Banana)
The names are located in the sheet, not in the workbook.
Then copy the sheet (inside or outside the workbook). Names are copied
automatically into the new sheet.
The chart in the copied sheet has no more names in its data-source, but the
basic values (e.g. ={160.200.240.320}).

XL-2007 has nearly the same effect: It changes the name into the cell
reference (Sheet1!$C$4:$C$7)

I am looking for a solution that evades any manual repair.
And without VBA!

Thanx, Holg.

 

[Jon Peltier]

If you move the sheet into another workbook, the names in the chart series
formulas remain intact. You need to move the sheet, then if the sheet came
from a multiple sheet workbook, close the workbook without saving changes
and reopen it. If the source workbook had just the one sheet, Excel closes
it, so you can simply reopen it.

If it's something you'll use frequently, save the sheet with chart as a
template, and add the new sheet based on this template.

- Jon

 

[Holger Gerths]

Great! Thanks a Lot!

If you move the sheet into another workbook, the names in the chart series
formulas remain intact. You need to move the sheet, then if the sheet came
from a multiple sheet workbook, close the workbook without saving changes
and reopen it. If the source workbook had just the one sheet, Excel closes
it, so you can simply reopen it.

If it's something you'll use frequently, save the sheet with chart as a
template, and add the new sheet based on this template.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______