create a new sheet and then rename all other sheets using unique names

[INN]

Hi
I am trying to create a new sheet and then rename other sheet with unique name, how can I do that? or can i check the names and then when I name a sheet to for example 1, I want excel to search for this name in all sheets and if it is there then either change the name of the new one to something else or change the other one. Thank you very much.

Sub abcd()
    ' adding sheet then rename the reset
    Worksheets.Add before:=Worksheets(1)
    Dim x As Integer
    Dim y As Integer
    x = Worksheets.Count
    For y = 1 To x
    Worksheets(y).Name = y
    Worksheets(y).Tab.ColorIndex = y + 4
    Next y
End Sub

 

[HaHoBe]

Hi INN,

maybe like this

Sub MrE_1222501_161420B()
' https://www.mrexcel.com/board/threads/create-a-new-sheet-and-then-rename-all-other-sheets-using-unique-names.1222501/
' working in the active workbook
' adding sheet then rename the reset
Dim intIndex As Integer
Dim lngNext As Long

Worksheets.Add before:=Worksheets(1)
lngNext = 1
For intIndex = 1 To Worksheets.Count
  Do While Evaluate("ISREF('" & lngNext & "'!A1)")
    lngNext = lngNext + 1
  Loop
  With Worksheets(intIndex)
    .Name = lngNext
    .Tab.ColorIndex = lngNext + 4
  End With
  lngNext = 1
Next intIndex
End Sub

or maybe

Sub MrE_1222501_161420B_Vers2()
' https://www.mrexcel.com/board/threads/create-a-new-sheet-and-then-rename-all-other-sheets-using-unique-names.1222501/
' working in the active workbook
' adding sheet then rename the reset
Dim intIndex As Integer

Worksheets.Add before:=Worksheets(1)
For intIndex = 1 To Worksheets.Count
  Do While Evaluate("ISREF('" & intIndex & "'!A1)")
    Worksheets(CStr(intIndex)).Name = intIndex + Worksheets.Count
  Loop
  With Worksheets(intIndex)
    .Name = intIndex
    .Tab.ColorIndex = intIndex + 4
  End With
Next intIndex
End Sub

Ciao,
Holger