renaming worksheets problem with dup names

[jek40]

i am using this code to rename worksheets based on a value in a cell on that sheet

Dim sname As String
Dim i As Integer
For i = 1 To 40
Sheets(i).Select
sname = Range("a1").Value
Sheets(i).Name = Format(sname, "mm-dd-yy")
Next i
End Sub

a problem arises because in the process of renaming the new sheet number 1-3 are the old sheets 38-40. (it will not work to leave 10 - 12 unchanged because data is being moved.)

any suggestions on an easy way to take care of this -- i was thinking of renaming the sheets to something generic and then renaming to the new names.

thanks
john

 

[Bruno_x]

Hi John,
Try this code

Sub RenameSheets()
Dim ws As Worksheet
Dim sname As String

For Each ws In Worksheets()
    ws.Select
    sname = Range("a1").Value
    ws.Name = Format(sname, "mm-dd-yy")
Next
End Sub

This code loops through all the worksheets in the workbook and rename all of them.

If you, for example, want to skip the first 3 sheets, use code like this :

Sub RenameSheets()

Dim ws As Worksheet
Dim sname As String

For Each ws In Worksheets()
    ws.Select
    If ws.Index > 3 Then
        sname = Range("a1").Value
        ws.Name = Format(sname, "mm-dd-yy")
    End If
Next
End Sub

edit : added the second code

 

[jek40]

Thanks Bruno
if i want to change the names of sheets 4 through 40 and have a total of 50 sheets how would the code look for that?

thanks
John

 

[Bruno_x]

Sub RenameSheets()

Dim ws As Worksheet
Dim sname As String

For Each ws In Worksheets()
    ws.Select
    'which sheets needs a new name :
    If ws.Index > 3 And ws.Index < 41 Then
        sname = Range("a1").Value
        ws.Name = Format(sname, "mm-dd-yy")
    End If
Next
End Sub