Macro to rename sheets based off cell value [Closed]

Report
-
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
-
Hello,

I have a workbook that I copy each month with dated sheets I would like to be able to rename each sheet based on the date.

3 replies

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Hi ExcelMiner,

Could you give some examples of sheet names?

Is sheet name text and date or only date? Date in which format?

Best regards,
Trowa
Posts
3
Registration date
Wednesday November 11, 2015
Status
Member
Last seen
April 18, 2016

For data collected on 11/20/2015 the sheet name would be "11-20".
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
You were talking about multiple sheets, but we can't give the same name to all sheets.

How would you like to handle that?
Sorry I guess I didn't explain it well. Each sheet would be a different date so a different sheet name.

Thank you
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
It's still not clear to me.

Do you have a workbook with multiple sheets like:
11-02
11-12
11-20
11-30
And you want a code that change the 11 into a 12?

OR

Do you have a list with dates and you want to create a sheet for each date in the list?

OR

Something else....
Posts
3
Registration date
Wednesday November 11, 2015
Status
Member
Last seen
April 18, 2016

the current workbook for November has sheets for 6 work days a week Monday thru Saturday. So the sheets are named 11-02, 11-03, 11-04, 11-05, 11-06, 11-07, 11-09, and so on till 11-28. each sheet is dated in cell A5 in mm/dd/yyyy format. Of course a sheet can't be named in this format so in cell AB4 [=A5] in mm-dd format. I thought after doing that I could use this macro.

Sub RenameTabs()
'Updateby20140624
For x = 1 To Sheets.Count
If Worksheets(x).Range("AB4").Value <> "" Then
Sheets(x).Name = Worksheets(x).Range("AB4").Value
End If
Next
End Sub

However the macro reed it in the original format mm/dd/yyyy. I would like it to read from a cell on the page like I tried to do.
Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
Although
"each sheet is dated in cell A5 in mm/dd/yyyy format. Of course a sheet can't be named in this format"
contradicts
"However the macro reed it in the original format mm/dd/yyyy"
you were almost there.

All you need to do now is let Excel know which format you want by using Format().

So change:
Worksheets(x).Range("AB4").Value
into:
Format(Worksheets(x).Range("AB4").Value, "mm-dd")


Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.