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]

Do you have anything else which has a name that might clash with the name you are trying to use for the new sheet?

 

[dUBBINS]

Is this only to create one sheet at a time, and the 1 to 100 is just a list of options that the sheet name can end in?

 

[dUBBINS]

Do you have anything else which has a name that might clash with the name you are trying to use for the new sheet?

Norie, doesn't it seem that since the On Error GoTo is not catching the naming error that the logic of the code is not working? And if that is true, then it is only trying to name the sheets "New Output1"; and perhaps "New Output2" since it works twice, which is weirder.

 

[throwawayaccount]

Is this only to create one sheet at a time, and the 1 to 100 is just a list of options that the sheet name can end in?

The purpose is to only create one new sheet each time the macro is run - if it's not unsuccessful it jumps to "skip:" outside of the loop.

 

[Norie]

dUBBINS

You could be on to something there.

Looking at the code again it appears that it'll only ever (try to) create one sheet at a time.

 

[throwawayaccount]

Norie, doesn't it seem that since the On Error GoTo is not catching the naming error that the logic of the code is not working? And if that is true, then it is only trying to name the sheets "New Output1"; and perhaps "New Output2" since it works twice, which is weirder.

Yeah, it always bombs out on the third attempt
no matter what. I even tried adding language to skip over the problematic iteration # but it's always just the third attempt.

 

[dUBBINS]

throw...are you only trying to create one sheet at a time? I think your code just needs some tweaks, that will result in avoiding skipping over errors. But just tell us what your code should be doing and I am sure it can be sorted. Norie is awesome at this stuff.

 

[throwawayaccount]

throw...are you only trying to create one sheet at a time? I think your code just needs some tweaks, that will result in avoiding skipping over errors. But just tell us what your code should be doing and I am sure it can be sorted. Norie is awesome at this stuff.

I just need a new sheet to be added each time the macro runs, with title in the form "New results #". So if there is already a "New results 1" and "New results 2" in there, I want it to come up as "New results 3". Ive done this in the past with other code I think but I can't figure out why this code isn't working, especially because it just fails on the third try each time.

Thanks for the help

 

[Joe4]

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]