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

Solution 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.

Solution 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

Solution 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

Solution 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.