When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
Macros can’t be undone so always save your file before running this macro. It can also be worthwhile using a different version number in case problems appear later. After running the macro check it hasn’t had any unintended consequences.
You can run this macro any time, but usually once you are finished doing all the sheet copying.
(If you are unsure about running macros see this post)
Below is an example of how the range names get duplicated.
Cell A1 was named test1 in Sheet1. When I copy Sheet1, cell A1 in the new sheet is also named test1, but at the worksheet level. You can see that in the Name Manager – image below.
The first name listed is scoped with the new Worksheet name, not Workbook as per the second name listed. The first name listed is in the copied sheet.
By the way, you can’t edit the scope of a name once it has been created. You have to delete the name and then re-create it with the correct scope.
The macro below goes through the Names collection and identifies Names with a Workbook scope (parent is the Workbook). When it finds one it goes through the Names collection again and finds any names that match the name that are scoped at the Worksheet level (parent is a Worksheet) and it deletes them. The sheet based names have the ! as part of their name.
If an error is encountered a message box is displayed.
Sub Remove_Duplicate_SheetBased_Names()
'removes all names with a Worksheet scope when there is
' the same name with a Workbook scope
Dim n As Name, nn As Name
Dim strName As String
On Error GoTo ErrorHandler
For Each n In Names
If TypeOf n.Parent Is Workbook Then
For Each nn In Names
If TypeOf nn.Parent Is Worksheet Then
strName = Right(nn.Name, Len(nn.Name) - InStr(nn.Name, "!"))
If n.Name = strName Then nn.Delete
End If
Next nn
End If
Next n
Exit Sub
ErrorHandler:
MsgBox "Error encountered - macro stopped", , "ERROR"
End Sub