I have 75 worksheets. Each sheet has a name established as a template. Each sheet name is in a column named "TABS" on a hidden sheet. I can change the name of each worksheet by entering a new name in cell C7 and it updates the new sheet name in the named range "TABS" on the hidden worksheet. I don't want any of the other sheets to have the same name. If I try to duplicate a sheet name this code goes into debug. How do I add to this code to look at the named range "TABS" and avoid duplicating a sheet name with a message that says "This sheet name already exist."
Option Explicit
Const origSheetName = "BLDG1"
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$C$7" Then Exit Sub
If Range("C7").Value = "" Then
Target.Parent.Name = origSheetName
Else
Target.Parent.Name = Range("C7").Value
End If
End Sub
Bookmarks