Rename sheets by cell value in date format

[Allhewrote]

I'm brand new to VBA and am trying to automate some repetitive tasks on workbooks for work purposes. I want to rename each sheet in a workbook to match a value in a cell in DD-MMM-YY format, different in each sheet. From what I've seen in a few different threads I've got this so far:

Sub Rename()
Dim ws As Worksheet
Set ws = ActiveSheet
For i = 1 To ThisWorkbook.Sheets.Count
Sheets(i).Activate
With ActiveSheet
ws.Name = Format(CDate(ws.Range("A1").Value), "DD-MMM-YY")
End With
Next i
End Sub

The code as written takes care of the active sheet when run, regardless of if it is worksheet 1, then skips to the end without formatting any of the subsequent sheets. How Do I make it stop and format each sheet? I keep seeing a mix of recursive loops, for loops, or even a "goto" function. Preference?

 

[Kaper]

You were pretty close, but overcomplicated things a bit

Sub Rename()
Dim ws As Worksheet
For each ws in ThisWorkbook.worksheets
  ws.Name = Format(CDate(ws.Range("A1").Value), "DD-MMM-YY")
Next ws
End Sub

Note that there is no need for wprksheet activation for renaming.

I prefer construction with for each element of worksheets collection (as above), but with sheet numbers will also do, so it could be written that way:

Sub Rename2()
Dim i As Long
For i = 1 To ThisWorkbook.Worksheets.Count
  With Worksheets(i)
    .Name = Format(CDate(.Range("A1").Value), "DD-MMM-YY")
  End With
Next i
End Sub

One of problems with original code - When you use with-endwith as above the references shall be starting with dot (as above).


PS. In real life I'd probably add some kind of error handler for situation where there are 2 sheets with the same date.
these 3 lines

  With Worksheets(i)
    .Name = Format(CDate(.Range("A1").Value), "DD-MMM-YY")
  End With

are the same as:

    Worksheets(i).Name = Format(CDate(Worksheets(i).Range("A1").Value), "DD-MMM-YY")

And of course as Worksheets(i) is repeated only twice probably one line version is better, but with longer code with-end with construction helps with readability of the code