Copy Tab and rename based on VBA input box

[Darren77]

Hello

I am quite new to VBA and can navigate myself around it (sort of) but have come up stuck with this issue. What i am attempting to do is

1. copy sheet6
2. rename the new sheet based on the input box text
3. if there is an error (sheet name exists) then go back to input box to rename.

I get a couple of issues in so much with the error handle a new sheet gets created no matter what which then puts me into the endless loop until i choose another name. The end result is a lot of duplicated sheets i dont need.

Please help!!

Dim myNewSheetName
tryagain:

On Error GoTo err

myNewSheetName = InputBox("Please enter new TAB name." & vbCrLf & _
"Please use SAP Reference as TAB name" & vbCrLf & _
"Example: 1.1.1")
On Error GoTo err
Sheet6.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = myNewSheetName
ActiveSheet.Tab.ColorIndex = 43
Exit Sub
err:
MsgBox "SAP already Exists"
Resume tryagain:

end sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Darren()
   Dim ShtName As String
   
   Do
      ShtName = InputBox("Please enter new TAB name." & vbCrLf & _
            "Please use SAP Reference as TAB name" & vbCrLf & _
            "Example: 1.1.1")
      If ShtName = "" Then Exit Sub
   Loop Until Evaluate("Isref('" & ShtName & "'!A1)") = False
   Sheet6.Copy , Sheets(Sheets.Count)
   With ActiveSheet
      .Name = ShtName
      .Tab.ColorIndex = 43
   End With
End Sub

 

[Darren77]

Hi & welcome to MrExcel.
How about

Sub Darren()
   Dim ShtName As String
  
   Do
      ShtName = InputBox("Please enter new TAB name." & vbCrLf & _
            "Please use SAP Reference as TAB name" & vbCrLf & _
            "Example: 1.1.1")
      If ShtName = "" Then Exit Sub
   Loop Until Evaluate("Isref('" & ShtName & "'!A1)") = False
   Sheet6.Copy , Sheets(Sheets.Count)
   With ActiveSheet
      .Name = ShtName
      .Tab.ColorIndex = 43
   End With
End Sub

Hello Fluff

thanks for the welcome and many thanks for the solution. This works perfectly.

Many thanks
Darren

 

[Fluff]

You're welcome & thanks for the feedback.