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 the 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