Incremental Sheet names

[kin1]

hello,

can someone help me with a formula that will check for the sheet names and if there's a duplicate, it will increment it by a value of 1?

example.
sheet1
if sheet1 already exsist that it will create a sheet2, and if sheet2 already exsist it will name it sheet3, etc.

thanks,

 

[Jonmo1]

Welcome to the baord..

Are ALL the sheets named in this same format..
Sheet1
Sheet2
Sheet3
etc
Sheet21

Or are there other sheetnames mixed in ?

And are they displayed in incrimental order from left to right?

And is it literally Sheet1 Sheet2? or something else but similar?
Post the EXACT sheet names please.

 

[kin1]

hi,

thanks for responding.

i have a macro that processes a single tab excel file, it renames this tab to attachment. but sometimes the file comes with multiple tabs, and i dont know how to rename duplicates.

i have activesheet.name = "attached", and it processes this file. say when it's done and i go to the next tab. it will try to name it "attached" again since that's how it's coded. i would like the second "attached" file to be attached1 or and so on.

i hope that makes sense.
thanks,

 

[Jonmo1]

Try something like this

For i = 1 to Sheets.Count
    On Error Resume Next
    If Sheets("Attached" & i).Name = "Attached" & i Then
        'Do Nothing, sheet exists already
    Else
        Sheets.Add
        ActiveSheet.Name = "Attached" & i
        Exit For
    End If
    On Error Goto 0
Next i