Rename multiple sheets in Excel workbook

Copper Contributor

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.

11 Replies

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 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 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.

Thank you SOOOOOO much!!! That saves me so much time : )  IT WORKED

:)

glad it worked for you.

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?

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?

I was starting with November 1 and it is starting with December 12??

hehe. I meant first of november, not december.

 

Did you try starting on 1/11/18?

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 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!!