Problem with duplicates when auto naming sheets

[fyrsten]

Hey

I have this problem with duplicates when i use a macro to auto name the sheets in the workbook. I have these cell references:

Every time i change E5 it won't change all of the sheetnames, ant this is because of the rule about no sheets can have the same name.

Sheet 1
E5 F5 G5 H5 I5
2000 : =E5+1 : =F5+1 : =G5+1 : =H5+1

Then Sheet 2 (B2=sheet1E5)
And sheet 3 (B2=sheet1F5)
And so on.

Then i use this macro to auto name the sheets (Under modules):
Sub NameWS()
On Error Resume Next
For Each ws In ThisWorkbook.Worksheets
ws.Name = Left(ws.Cells(2, 2).Value, 31)
Next
On Error GoTo 0
End Sub

And then this macro under each sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
NameWS
End Sub

This is the example: http://s000.tinyupload.com/index.php...79914936788269

Is there a way how to make the sheetnames turn at the same time?

[NeedForExcel]

The Sheet names are indeed changing automatically at my end..

[fyrsten]

Yes, but if you change E5 from 2000 to 2001, then only sheet5 will change to 2005 right? And if you change E5 to 2006 instead, then they will all change, right?

[NeedForExcel]

Here!

Try This

Sub NamesWS()

    Application.ScreenUpdating = False
    
    Dim SheetCount As Integer, Counter As Integer, WSName As String, NewName As String
    
    SheetCount = ThisWorkbook.Worksheets.Count
    
    For Counter = 1 To SheetCount
    
        Worksheets(Counter).Activate
        WSName = Worksheets(Counter).Name
        
        If WSName <> "Start" Then
            NewName = Cells(2, 2).Value
            ActiveSheet.Name = NewName & "X"
        End If
    
    Next Counter
    
    For Counter = 1 To SheetCount
    
        Worksheets(Counter).Activate
        WSName = Worksheets(Counter).Name
        
        If WSName <> "Start" Then
        
            WSName = Application.WorksheetFunction.Substitute(WSName, "X", "")
            ActiveSheet.Name = WSName
        End If
    
    Next Counter
    
    Worksheets("Start").Activate
    
    Range("E5").Activate
    
    Application.ScreenUpdating = True

End Sub

And Dont Forget This -

Private Sub Worksheet_Change(ByVal Target As Range)
    NamesWS
End Sub

[fyrsten]

That worked perfectly.. Thanks!

[NeedForExcel]

Glad It Worked!