Forum Discussion
Rename multiple sheets in Excel workbook
I have a sheet for each day of the year and I make 3 quarterly workbooks. I want to change the date for each sheet (tab) made without clicking and renaming them all. I tried to do a VBA code but failed miserably. Any help would be appreciated.
Do you want to make a new workbook, and then add 90 (?) worksheets and have them all dates? Or just create a new worksheet each day and have a button that renames it for you?
I would to have workbook with running dates
Eg 20 Saturday August 2018
Before month calendar after month calendar
Who is rostered on for Saturday or on call
21sunday August 2018
The calendars
Who is rostered
So I can print off cut on stick into old dairy with out wasting paper.
- I have created a new workbook with 90 worksheets. I need to date them all now or is there a way to start over have them dated in calendar order?
hia,
this should work.. you will need to add it as a module and then run it..
It will change the name of every worksheet in the workbook, starting with the most left sheet and working right.
Sub rename()
Dim Mydate As Variant
Mydate = InputBox("dd/mm/yy")
For Each sht In ThisWorkbook.Worksheets
sht.Name = Format(Mydate, "dd-mm-yy")
Mydate = DateAdd("d", 1, Mydate)
Next sht
End SubIt will ask you for a date to start, I know it works with dd/mm/yy so use that.. if you want the date format of the name to be different then you can edit the line:
sht.Name = Format(Mydate, "dd-mm-yy") change the date mask to match what you want.. just remember that you cant use / as that's an illegal character in sheet names.
Normal warnings.. make sure you have a copy of your workbook saved before you press any buttons.. there is no undo with VBA. There is also no error checking with this, so if you do edit the mask and add something it doesn't like it will just fail.
Hope that helps.
Thank you SOOOOOO much!!! That saves me so much time : ) IT WORKED
