Automatically name tabs with date
Hi,
I'm relatively new to Excel and only know the basic =SUM operations etc. I am building a workbook that my wife will use as an appointment book for her hair salon.
I want each workbook to contain one month's worth of sheets, with a sheet containing one day's appointments. I need an easy way to name each sheet tab with the date, for example "01/08/09 Tue" for the first sheet and then all the following sheets will follow on date wise ie "02/08/09 Wed".
Hope this makes sense, I cant possibly name each sheet individually!
TIA
Put this code in ThisWorkbook and type in cell A2 the datelook also to my workbook and take a tourPrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) For Each ws In Worksheets ActiveSheet.Name = Format(Range("A2").Value, "dd.mm.yy ddd") Next End Sub
Thanks,
Can you tell me where I type the code
Sorry, very new at this
Press Alt F11 > VB editor > look for ThisWorkBook and put the code thereOriginally Posted by rikkwood
Thanks,
Can you tell me where I type the code
Sorry, very new at this
Re: Automatically name tabs with date
Awesome!
You sir are a genius!
Re: Automatically name tabs with date
I don't suppose there is a way to automatically populate a full month's sheets so I won't have to enter each day?
TIA
You will also find it difficult to manage, navigating to each sheet will be difficult.
A better way would be to have an appointments sheet and a table of appointments that will populate the sheet by date.
I'll seee i I can knock up an example.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Like this ( sorry in dutch ) change the year in sheet " jaaroverzicht " cell E4
Re: Automatically name tabs with date
How do you do that?!
Really sorry to keep bothering you but how would I make that work for my own workbook that has one day per sheet?
Sorry
new workbook only insert the date in cell A1 and shift F11 for a new sheet with date tab . sorry for the writing errors
You can also use this codePrivate Sub Workbook_NewSheet(ByVal Sh As Object) Application.ScreenUpdating = False Sheets("start").Cells.Copy Destination:=Sh.Range("A1") Sheets("start").Range("A1").Value = Sheets("start").Range("A1").Value + 1 ActiveSheet.Name = Format(Range("A1").Value, "dd.mm.yy ddd") Application.ScreenUpdating = True End Sub
Last edited by trucker10; 08-23-2009 at 07:08 PM.
Re: Automatically name tabs with date
Amazing Trucker10!
Thanks for all the help so far, and to think I only posted last night and have solved what I thought was an impossible problem already!
Cheers!
Ooooh, one last thing (I promise). Is there any way I can hide the date from view in the B2 cell? May cause confusion.
Last edited by rikkwood; 08-24-2009 at 05:13 AM.
Something like this
( sorry for late answer i' m on the road )
Last edited by trucker10; 08-24-2009 at 12:36 PM. Reason: new posting
I have created a user form, press Ctrl TCan I make a sheet that has links to all the seperate sheets without doing each individual link?
new workbook only insert the date in cell A1 and shift F11 for a new sheet with date tab . sorry for the writing errors
You can also use this codePrivate Sub Workbook_NewSheet(ByVal Sh As Object) Application.ScreenUpdating = False Sheets("start").Cells.Copy Destination:=Sh.Range("A1") Sheets("start").Range("A1").Value = Sheets("start").Range("A1").Value + 1 ActiveSheet.Name = Format(Range("A1").Value, "dd.mm.yy ddd") Application.ScreenUpdating = True End Sub
I found this thread as it was exactly what I was looking for, coincidentally for my better half too.!
What I want to also do is add a print area function to the code, either by
A - Setting the print area on the 'start' sheet, before the user hits Shift +F11 to add additional sheets, or
B - Allow the user to set the print area across multiple sheets when they print.
Also, one other thing I have noticed, if I simply click on the next tab ( to insert a new tab), it adds the new tab in date order, however, if I use the Shift+F11 option, it adds the tab to the left of where the last sheet was inserted, so I effectively get the dates from end of month to beginning instead of vice verse.
Thanks,
Mike
Last edited by Mike_Dean; 03-30-2010 at 01:51 AM.
Re: Automatically name tabs with date
I have a spreadsheet that I do every month. It has on average 25 worksheets. I have to name the worksheet like this Thur. 7/1/10, Fri. 7/2/10 and so on excluding Saturday and Sunday. Is there a way to do this automaticlly or do I have to rename each sheet?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks