Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

     If wb Is Nothing Then Set wb = ThisWorkbook
     On Error Resume Next
     Set sht = wb.Sheets(shtName)
     On Error GoTo 0
     SheetExists = Not sht Is Nothing
 End Function
 

Private Sub CommandButton2_Click()

Dim SheetName As String
Dim shExists As Boolean

Do

SheetName = InputBox("Please enter the LAST NAME of the DTS.", "Add Sheet")
If SheetName <> "" Then
shExists = SheetExists(SheetName)
    If Not shExists Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = SheetName
        MsgBox "The " & (SheetName) & " sheet was successfuly made. Please don't forget to CHANGE the name under the field DTS NAME.", , "Result"
    Else
        MsgBox "The last name already exists, please enter a NUMBER at the end of the LAST NAME to distinguish it from the existing one. For example lastname1 or lastname2", vbOKOnly + vbInformation, "Name"
    End If
Else
    MsgBox "You did not enter anything.", vbOKOnly + vbInformation, "Warning"

'Exit Function
End If
End Sub

    Sheets("Sheet1").Copy before:=Sheets(1)
    ActiveSheet.Name = SheetName