Copy template worksheet, rename it only if the name is not already taken

[AlexisChch]

Hi all,

I'm new to this forum.

It seems simple but I've searched a lot online and couldn't find something that works. I searched a lot in this forum and I found lot of suggestions but nothing worked for my case.

I'm trying to copy a template worksheet (called "Template") and rename it using a command button. I have managed to achieve this.
However, I would like to rename the new worksheet only if the name is not already exists.

If the name of the worksheet exists already in another worksheet I would like to

1. get a pop up message saying something like this: "Entry 01 already exists, please exit and insert Entry 02" and then
2. I would like the code to prevent from creating a copy of the "Template".

Right now I have this code:

Private Sub cmdSV1_Click()
With Sheets("Front Sheet")
    If .Range("B17") = "" Then
        MsgBox "Please enter the Site Visit reference number in cell B17 before you 'Insert the Entry'"
        .Activate
        .Range("B17").Select
       Exit Sub
    End If
End With
Sheets("Template").Copy After:=Sheets("Front Sheet")
ActiveSheet.Name = Sheets("Front Sheet").Range("B17")
End Sub

This code works exactly as I want. What it does is:

• Creates a new worksheet based on a copy of the "Template" worksheet
• rename the worksheet to what is in range("B17") of the "Front Sheet" Worksheet (Range B17 is always the number "1").

When I click the command button for the first time it works great and does what I want. However, once I click the same command button for the second time I get the 1004 error. "That name is already taken. Try a different one. End, Debug." Then the code creates a new worksheet with the name "Template (2)".

I would like when I click the command button to get a pop up message if the worksheet name already exists in the spreadsheet.

*PS. The "With ..End With" part of the code above works as I want but is irrelevant to my question. I put it there so I can get a pop up message when a user is trying to insert a new entry without filling the "B17" cell from the "Front Sheet".

Any help would be much appreciated.
Many thanks

 

[AlexisChch]

Update
I managed to find my way around. Perhaps other people may find this helpful.

Private Sub cmdSV1_Click()
Dim strName As String
  strName = Sheets("Front Sheet").Range("B17")
    For Each Worksheet In Worksheets
    If Worksheet.Name = "1" Then
      MsgBox "An existing tab is named " & strName & "." _
      & vbCrLf & "Please insert the correct site visit entry"
      Exit Sub
    End If
  Next
With Sheets("Front Sheet")
    If .Range("B17") = "" Then
        MsgBox "Please fill in a Site Visit reference number(cell B17) before you 'Insert a Site Visit Entry'"
        .Activate
        .Range("B17").Select
       Exit Sub
    End If
    End With
Sheets("Template").Copy After:=Sheets("Front Sheet")
ActiveSheet.Name = Sheets("Front Sheet").Range("B17")
End Sub

The above works perfectly.
However, I now need to find a way where I can copy the "Template" worksheet when it is hidden. The above code works only when the "Template" worksheet is unhidden.

If I hide the "Template" worksheet and click the button to copy the "Template" worksheet I will get a new worksheet (named "1") but then my "Front Sheet" will disappear.

Any idea would be much appreciated.

 

[AlexisChch]

I'm sorry for the repetitive posts.

I couldn't find an option to edit my original post above and hence I have three posts in a row.

I find the solution myself and the code I used it does the job for me.

Private Sub cmdSV1_Click()
Dim strName As String
  strName = Sheets("Front Sheet").Range("B17")
    For Each Worksheet In Worksheets
    If Worksheet.Name = "1" Then
      MsgBox "An existing tab is named " & strName & "." _
      & vbCrLf & "Please insert the correct site visit entry"
      Exit Sub
    End If
  Next
With Sheets("Front Sheet")
    If .Range("B17") = "" Then
        MsgBox "Please fill in a Site Visit reference number(cell B17) before you 'Insert a Site Visit Entry'"
        .Activate
        .Range("B17").Select
       Exit Sub
    End If
    Worksheets("Template").Visible = xlSheetVisible
    End With
Sheets("Template").Copy After:=Sheets("Front Sheet")
ActiveSheet.Name = Sheets("Front Sheet").Range("B17")
Worksheets("Template").Visible = xlSheetHidden
End Sub

Many thanks