I've been working on a Microsoft Excel 2007 spreadsheet for several days. I'm working from a master template like sheet and copying it to a new sheet repeatedly. Up until today, this was happening with no issues. However, in the middle of today this suddenly changed and I do not know why. Now, whenever I try to copy a worksheet I get about ten dialogs, each one with a different name range object (shown below as 'XXXX') and I click yes for each one:
A formula or sheet you want to move or copy contains the name 'XXXX', which already exists on the destination worksheet. Do you want to use this version of the name?
- To use the name as defined in destination sheet, click Yes.
- To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box.
The name range objects refer to cells in the sheet. For example,
E6 is called name range
PRE on multiple sheets (and has been all along) and some of the formulas refer to
PRE instead of
$E$6. One of the 'XXXX' above is this
PRE. These name ranges should only be resolved within the sheet within which they appear. This was not an issue before despite the same name range existing on multiple sheets before. I want to keep my name ranges.
What could have changed in my spreadsheet to cause this change in behavior? I've gone back to prior sheets created this way and now they give the message too when copied. I tried a different computer and a different user and the same behavior is seen everywhere. I can only conclude something in the spreadsheet has changed. What could this be and how can I get back the old behavior whereby I can copy sheets with name ranges and not get any warnings?
Looking in the
Name Manager I see that the name ranges being complained about show twice, once as scope
Template and again as scope
Workbook. If I delete the scope
Template ones the warning goes away on copy however, I get a bunch of #REF errors. If I delete the scope
Workbook ones, all seems okay and the warnings on copy go away too, so perhaps this is the answer, but I'm nervous about what effect this deletion will have and wonder how the
Workbook ones came into existence in the first place.
Will it be safe to just delete the
Workbook name manager scoped entries and how might these have come into existence without my knowing it to begin with?