Why does Excel now give me already existing name range warning on Copy Sheet?

Question

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?

Answer 1

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.

Answer 2

Sub TOOLS_DELETENAMEDRANGE()

  Dim nm As name
  On Error Resume Next

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

  On Error GoTo 0

End Sub

Answer 3

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

Answer 4

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
    vJunkName.Delete
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.

Answer 5

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.

Answer 6

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.