Add new sheet error 1004 "Cannot rename a sheet to the same name as another sheet..."

[throwawayaccount]

Getting this error and after research cannot find a solution. Consistently, this macro works fine until the third time I run it, regardless of the name I give the sheets. It always bombs out despite the "on error goto nextb" giving me the following error:

Microsoft Visual Basic

Run-time error '1004':

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

This is the only workbook I have open, there are only two other sheets (none hidden, very-hidden, etc.). Can anyone help?

Sheets.Add After:=Sheets(Sheets.Count)
For b = 1 To 100
    On Error GoTo nextb
    ActiveSheet.Name = "New Output " & b
    GoTo skip
nextb:
Next b
skip:

 

[Norie]

How about using a variable declared as Static?

Something like this.

Sub Button1_Click()
Dim ws As Worksheet
Static Num As Integer

    Set ws = Worksheets.Add
    Num = Num + 1
    ws.Name = "New Output " & Num

End Sub

 

[throwawayaccount]

Are there any other sheets besides your "New Results #" sheets?
I am trying to figure out if there is a correlation between the last number used, and the total number of sheets in your workbook.

If there is always a correlation, like there will be two other sheets so the number for any newly added sheet will always be two less than the total number of sheets in the workbook, no loop is necessary. Just run this:

    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "New Output " & Sheets.Count [B][COLOR=#ff0000]- 2[/COLOR][/B]

This will work, I'll use this. Thanks. I'm still interested in knowing why my code didn't work though. I'm trying to get better at VBA so it's nice to understand why certain code fails

 

[jsemenak]

Getting this error and after research cannot find a solution. Consistently, this macro works fine until the third time I run it, regardless of the name I give the sheets. It always bombs out despite the "on error goto nextb" giving me the following error:

Microsoft Visual Basic

Run-time error '1004':

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

This is the only workbook I have open, there are only two other sheets (none hidden, very-hidden, etc.). Can anyone help?

Sheets.Add After:=Sheets(Sheets.Count)
For b = 1 To 100
    On Error GoTo nextb
    ActiveSheet.Name = "New Output " & b
    GoTo skip
nextb:
Next b
skip:

I don know why your name is not accepted. For me is more interesant why ON ERROR statement does not work. I have similar problem in my sub

Worksheets.Add After:=Worksheets(Worksheets.Count)
On Error Resume Next
Worksheets(Worksheets.Count).Name = sName

1st line of my code add new worksheet
then I want to rename it to sName. But when sName contains invalid string for example "p/*" I want to ignore renaming via On Error resume Next (in this case I want to left automatically added name by Method ADD like "Sheet15")
Actually this code indeed creating new sheet named "Sheet15", But On Error statement not work. It is also your issue. If your names are not properly good for name of sheets your On Error statement shall allow to ignore renaming. It is maybe feature of Excel that on error statement doesn't work for renaming sheet with wrong name.

 

[dUBBINS]

Getting this error and after research cannot find a solution. Consistently, this macro works fine until the third time I run it, regardless of the name I give the sheets. It always bombs out despite the "on error goto nextb" giving me the following error:

Microsoft Visual Basic

Run-time error '1004':

Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.

This is the only workbook I have open, there are only two other sheets (none hidden, very-hidden, etc.). Can anyone help?

Sheets.Add After:=Sheets(Sheets.Count)
For b = 1 To 100
    On Error GoTo nextb
    ActiveSheet.Name = "New Output " & b
    GoTo skip
nextb:
Next b
skip:

I am not sure why your code is not working as written, your logic is solid, but it may be that VBA just is hung up on something in the language. Here is how I solved the same problem in the past (in case it helps you learn):

Dim workSH As Integer
Dim worksheetEXISTS As Boolean
workSH = Application.Sheets.Count
worksheetsexits = False
    For n = 1 To workSH
        If Worksheets(n).Name = t Then
            worksheetEXISTS = True
                t = t + 1
                i = i + 1
    Exit For
        End If
Next n
    If worksheetEXISTS = False Then
    End If

 

[dUBBINS]

I don know why your name is not accepted. For me is more interesant why ON ERROR statement does not work. I have similar problem in my sub

Worksheets.Add After:=Worksheets(Worksheets.Count)
On Error Resume Next
Worksheets(Worksheets.Count).Name = sName

1st line of my code add new worksheet
then I want to rename it to sName. But when sName contains invalid string for example "p/*" I want to ignore renaming via On Error resume Next (in this case I want to left automatically added name by Method ADD like "Sheet15")
Actually this code indeed creating new sheet named "Sheet15", But On Error statement not work. It is also your issue. If your names are not properly good for name of sheets your On Error statement shall allow to ignore renaming. It is maybe feature of Excel that on error statement doesn't work for renaming sheet with wrong name.

JS...Would you be so kind as to post this in a new thread? You will get answers much faster. This thread has been solved, and your question is slightly off topic from the original post. Thank you kindly.

 

[throwawayaccount]

I am not sure why your code is not working as written, your logic is solid, but it may be that VBA just is hung up on something in the language. Here is how I solved the same problem in the past (in case it helps you learn):

Thanks for the help on this guys. I think I figured out the issue I was having, and a solution. You can't just use "On Error GoTo" operationally like I was, because it doesn't clear the Err.Number with a "Resume" statement. Therefore, the second time it has an error, the Err.Number is already in place and it causes a run-time error. This explains why it was happening on the third time every time. First time, there is no error. Second time, there is one error. Third time, there is already an error so the program bombs out. In order to circumvent this, code needs to be in the form:

Sheets.Add After:=Sheets(Sheets.Count)
For b = 1 To 100
    On Error GoTo err_nextb
    ActiveSheet.Name = "New Output " & b
    GoTo err_skip
nextb:
Next b
skip: 

'rest of code

exit sub

err_nextb:
resume nextb

err_skip:
resume skip

end sub

"On Error GoTo" needs to be used with proper error handling. If on that error you want to exit the sub you can. Otherwise, you need to use the "Resume" command. Here is the guide I used: Error Handling In VBA