Copy Tab and rename based on VBA input box

Darren77

New Member
Joined
Jun 29, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi & welcome to MrExcel.
How about
VBA Code:
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
 
Upvote 0
Solution
Hi & welcome to MrExcel.
How about
VBA Code:
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
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top