Duplicate Sheet Names

[Chewyhairball]

Hi Guys,
I have this code that creates a new sheet and when run it asks for a new sheet name. It basically copies a hidden sheet called 'blank template' and renames it.
If I input the name it creates the new sheet
If I cancel it just cancels the code
If I use a name I have used before it creates a new tab called "blank template 2, 3, 4 etc)
I don't want it to do this but rather if there is already a sheet with the same name I would like it to put up a message ' This name already exists, please use a unique ID' followed by another input box to put the new name in.

thanks in advance.


Sub NewCheck()
'
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Sheet20.Visible = xlSheetVisible
Sheets("Blank Template").Select
Sheets("Blank Template").Copy After:=Sheets(Sheets.Count)

Range("B5").Select
Dim C As Range
Set C = ActiveCell


val1 = InputBox("Please enter the new System Check or Calibration Check ID", "New System/Calibration Check ID")
If val1 = "" Then
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Sheet20.Visible = xlSheetHidden
Application.DisplayAlerts = True

Sheets("New check").Select

Exit Sub
Else
C.Value = val1
End If

Sheet20.Visible = xlSheetHidden
ActiveSheet.Protect
End Sub

 

[Fluff]

How about

Sub Chewyhairball()
   Dim IsValid As Boolean
   Dim ShtName As String
   
   Do Until IsValid
      ShtName = InputBox("Please enter the new System Check or Calibration Check ID", "New System/Calibration Check ID")
      If ShtName = "" Then Exit Sub
      If Evaluate("isref('" & ShtName & "'!A1)") Then
         MsgBox "This name already exists, please use a unique ID"
      Else
         IsValid = True
      End If
   Loop
   With Sheet20
      .Visible = xlSheetVisible
      .Copy , Sheets(Sheets.Count)
      .Visible = xlSheetHidden
   End With
   ActiveSheet.Name = ShtName
   Range("B5").Value = ShtName
End Sub

 

[Chewyhairball]

Thank you Fluff.

That is perfect!...and you tidied up my very clunky code
Much appreciated

Rory

 

[Fluff]

You're welcome & thanks for the feedback.