Macro to rename sheets based off cell value [Closed]

Report
-
TrowaD
Posts
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 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
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
391
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
ExcelMiner
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".
TrowaD
Posts
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
391
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
TrowaD
Posts
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
391
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....
ExcelMiner
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
2587
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 23, 2020
391
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.