Programmatically renaming sheets with variables

[tourless]

Hi Folks,

I'm looking for a way to rename all the sheets in a workbook. We have many workbooks that contain a worksheet for each week of the year. They are all nemd in the following convention... "WE 01-07-23", "WE 01-14-23", etc. When preparing new workbooks for the coming year, we make copies of the current workbooks and have to rename all the sheets manually. Is it possible to rename them all with a bit of code? I've looked around the forums and have found posts that speak to renaming sheets but only by taking a value from a given cell or cells, but nothing that looks to my problem.

My thought is to name the first sheet appropriately then programmatically rename the remaining sheets. My problem is I don't know enough about Excel to know where to find and access an existing worksheets Name value, place it in a variable and manipulate it from there. Can anyone help point me in the right direction? -thanks.

 

[Joe4]

Are there any sheets in your workbook before the first Week-Ending sheet (i.e. "WE 01-07-23")?

Will all the sheets needed for the year already be in the workbook at the time you want this code to run, so that we just need to rename all the existing sheets?

 

[tourless]

Are there any sheets in your workbook before the first Week-Ending sheet (i.e. "WE 01-07-23")?

Will all the sheets needed for the year already be in the workbook at the time you want this code to run, so that we just need to rename all the existing sheets?

1. No there are not. The first sheet in the workbook is always the week ending date of the first week of the year (week ending on Saturday).
2. Yes. All 52 sheets already exist in the workbooks. There is no need to count and/or create new sheets.

 

[Joe4]

Excellent. I think the following code should do what you want:

Sub MyNameSheets()

    Dim n1 As String
    Dim d1 As Date
    Dim i As Long
    Dim n2 As String
    
'   Get date from first sheet name
    n1 = Mid(Sheets(1).Name, 4)
    d1 = DateSerial(Right(n1, 2), Left(n1, 2), Mid(n1, 4, 2))
    
'   Loop through all sheets
    If Sheets.Count > 1 Then
        For i = 2 To Sheets.Count
'           Rename sheet
            n2 = "WE " & Format(d1 + ((i - 1) * 7), "mm-dd-yy")
            Sheets(i).Name = n2
        Next i
    End If
    
    MsgBox "Macro complete!"
    
End Sub

 

[tourless]

That's brilliant! Thank you very much!

 

[Joe4]

You are welcome.
Glad I was able to help!