Setting worksheet name not working?

[jkwleisemann]

I'm trying to set the name of a worksheet using the following code, and it's failing to process for some reason. Can anybody help me out with this?

Sub CreateNew()
    intTabs = Worksheets(1).Cells(18, 2).Value
    For i = 1 To intTabs
        strWsName = InputBox("Please name Sheet" & i & ".", "Sheet" & i & " Name", "Sheet" & i)
        If i > 1 Then Worksheets(5).Copy After:=Worksheets(Worksheets.Count)
        Worksheets(Worksheets.Count).Name = strWsName
    Next
End Sub

Also, if there's any way for me to try and set the name when I'm doing the Copy method, that'd be great. I'd like to try and minimize the number of chances to throw "errors" that aren't.

 

[John_w]

Try this more robust code. It handles the possibility of the user entering a sheet name which already exists, looping until a unique name is entered, and clicking Cancel on the InputBox prompt.

Sub CreateNew()
    Dim intTabs As Integer, i As Integer, strWsName As String, sheetNameExists As Boolean, cancelled As Boolean
    intTabs = Worksheets(1).Cells(18, 2).Value
    i = 0
    Do
        i = i + 1
        Do
            strWsName = InputBox("Please name Sheet " & i & ".", "Sheet " & i & " Name", "Sheet" & i)
            If strWsName = "" Then
                cancelled = True
            Else
                sheetNameExists = SheetExists(strWsName)
                If sheetNameExists Then MsgBox "The sheet named '" & strWsName & "' already exists"
            End If
        Loop Until cancelled Or Not sheetNameExists
        If Not cancelled Then
            Worksheets(5).Copy After:=Worksheets(Worksheets.Count)
            Worksheets(Worksheets.Count).Name = strWsName
        End If
    Loop While i < intTabs And Not cancelled
End Sub

Private Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(sheetName)
    On Error GoTo 0
    SheetExists = Not ws Is Nothing
End Function

You can't copy and name a sheet in one step.

 

[jkwleisemann]

Had a feeling I couldn't do the copy and name in one step.

And actually, I just discovered that what created the error was that I had the workbook protected. So nice of Excel to elaborate on these sort of things!