Aug 21 2018 11:28 AM
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.
Aug 21 2018 02:02 PM
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?
Aug 22 2018 06:20 AM
Aug 22 2018 07:06 AM
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 Sub
It 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.
Aug 22 2018 08:17 AM
Thank you SOOOOOO much!!! That saves me so much time : ) IT WORKED
Aug 22 2018 10:14 AM
Well it worked the first time i used it just fine. So I created my next quarter....it has 90 tabs and when i run it i put in 11/01/18 as my start date and it puts in 12/12/18 then 1/22/19 then runs normal after that?? I've removed and tried again multiple times and it comes out the same each time?? Any ideas why?
Aug 22 2018 12:08 PM
Mmm thats pretty odd.
is that the 11th of jan or the first of december you are starting on? guessing its the first of december?
can you try entering 01/11/18 and see what it does?
Aug 22 2018 12:14 PM
I was starting with November 1 and it is starting with December 12??
Aug 22 2018 12:30 PM
hehe. I meant first of november, not december.
Did you try starting on 1/11/18?
Aug 22 2018 01:22 PM
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.
Aug 22 2018 01:41 PM
I did try that and it did'nt work. I had to go back to 08/01/18 to get it to put the end of october up to 1/16/2019. Better than what it was, just find it strange because it worked perfect the first time. Thank you for all your help!!