Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
Did you ever find out why these Workbook-scope names were appearing? I'm seeing the exact same thing - I can fix it by deleting the Workbook names, but I'd be happier if I knew what caused them. – Neil Vass Dec 17 '12 at 20:57
No, I just deleted per the answer I gave below. It would be nice to know why this occurs. – WilliamKF Dec 18 '12 at 14:25
up vote 3 down vote accepted

Open the Name Manager and find the named items being complained about and delete all those with scope Workbook and the issue with copying sheets goes away and the formulas remain intact.

share|improve this answer

  Dim nm As name
  On Error Resume Next

  For Each nm In ActiveWorkbook.Names
    If (MsgBox("Delete name- " &, vbYesNo) = vbYes) Then  
    End If

  On Error GoTo 0

End Sub
share|improve this answer
This is useless... you can you the Name Manager for that. What should be the reason for using such a code? – codea Aug 4 '14 at 16:57
How can I adapt this to only delete ranges that aren't in the workbook scope? – Devil's Advocate Jan 27 at 21:30

This is not an error, but a warning a conflict exists and offers a choice on what to do

When you copy a range that includes reference to a (typically worksheet scope) named range, and that same name exists on the destination sheet, how is Excel to know which name you want to use in the result?

As to whats changed, I suggest you review all the names in your workbook, especially for scope.
Name Manager - Ctrl-F3

share|improve this answer
I've updated the question to use the word 'warning' instead of 'error', but my question remains. – WilliamKF Sep 17 '12 at 13:55

These names are mostly the result of a data download through a third-party API. The APIs typically use those names as placeholders for cell referencing.

Once you have processed some data, these names are mostly left behind as a general user does not clean up the hidden (or very-hidden) sheets. The difficulty associated with these names is that they also do not show up in the NAMES box and therefore cannot be deleted through that option.

One way that I go about it is to program it through a VBA script. Sample below

Sub do_loop_names()
Dim vJunkName As Name
Debug.Print ThisWorkbook.Names.Count

For Each vJunkName In ThisWorkbook.Names
Next vJunkName
End Sub

Do note this script will delete ALL names from the workbook, so if there are certain defined names you'd like to keep, please put in the exceptions in the code.

If someone has a more efficient solution, please let me know.

share|improve this answer
I know hidden sheets. But what are very hidden sheets? – nixda Nov 4 '13 at 9:31
@nixda You can hide or unhide sheets by right clicking on the sheet tabs as you know. You can do the same thing through VBA, and through VBA you have another option to make sheets very hidden which means they are not available to unhide when right clicking on the sheet tabs. – Levi Nov 4 '13 at 23:14
@Levi gave a good explanation. very hidden sheets are invisible on the workbook unless set visible through VBA. Hidden sheets can be viewed through the spreadsheet Hide / Unhide option but the veryhidden feature makes these sheets inaccessible unless through VBA. – Viquar Nov 12 '13 at 13:38

Same problem here with 27 mystery named ranges. I used Ctrl+F3 (as recommended in another answer) to view the list of named ranges (which I had never set up!). There were many since I had been copying the worksheet tab many times and just clicking "yes" to work around the message. I highlighted and deleted all the named ranges (one little screen at a time, couldn't Select All). The next time I tried to copy the worksheet, the copy succeeded without the warning message.

Be sure to copy your original file before trying this.

share|improve this answer

I've had the same problem, and tried each solution provided, with varying success:

1) I opened the Names Manager and, sure enough, there were a bunch of Names I never created; however, they were NOT the names I was being warned about. I deleted them anyway to get rid of the clutter, but that wasn't the solution.

2) I tried the code provided by Viquar (start "Sub do_loops_names"). I think part of the code was missed, because I can't see how it actually does anything and, sure enough, nothing happened.

3) I tried the code provided by user345338 (starts with "Sub TOOLS_...") - PERFECT. It walked me through all the hidden names that somehow were stuck in the workbook and deleted them. Thank you very much, user345338 (whoever you are!).

BTW, to "codea" - user345338's code is not "useless" as it resolved my issue.

share|improve this answer

You must log in to answer this question.

protected by Community Sep 15 '15 at 21:23

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged .