Sequentially dated sheet names

[Mike]

I have 52 sheets in my workbook, and would like to type in a date in the first sheet, and have the rest of the sheets auto fill for the rest of the year. Any clues
Thanks

 

[Frank Kabel]

Hi
do you want to NAME the sheets or do you want these values in a cell of
each sheet?

 

[Mike]

I would like to name the sheets

 

[Mike]

I was wanting the sheet name to be the date of the last day of the week, and have the rest of the 52 sheets auto fill for the rest of the year. The workbook is a weekly report kind of thing. Sorry I wasn't clearer.
Mike Case
Lakeland, Florida

 

[JE McGimpsey]

One way:

This will name the sheet with the date of the following Saturday.

Public Sub RenameSheets()
Dim i As Long
Dim nDate As Date
nDate = Date + 7 - WeekDay(Date) 'Saturday
For i = 1 To Worksheets.Count
Worksheets(i).Name = Format(nDate, "dd-mmm-yyyy")
nDate = nDate + 7
Next i
End Sub

or, if you want to enter the first sheet name manually:

Public Sub RenameSheets()
Dim i As Long
Dim nDate As Date
nDate = CDate(Worksheets(1).Name) + 7
For i = 2 To Worksheets.Count
Worksheets(i).Name = Format(nDate, "dd-mmm-yyyy")
nDate = nDate + 7
Next i
End Sub

Change the format to suit.

 

[Mike]

Cool! That worked great! Now if you could tell me how to get a cell to reflect the date in the sheet name
Thanks again
Mike Cas
Lakeland, Florida

 

[JE McGimpsey]

One way:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

 

[Mike]

Thanks so much! It works perfectly
Mike Case
Lakeland, Florida