Solved How to automatically name a worksheet from the date in a cel

October 4, 2012 at 13:34:21
Specs: Windows Vista
What I am trying to acomplish is a work log. I have in row A5 through A11 the dates of the week and these are calculated from the date I put in B3. So for example if I put 10/12/2012 in B3 then A5 will show "Saturday 6 October 2012", A6 will show "Sunday 7 October 2012" and so forth all the way to cell A11 which has "Friday 12 October 2012" which is the date that matches B3. Of course in the worksheet A3 has the words "Weekending"

I do have codes in cells A5 through A11. Basically the formulas I have is, I put an actual date in B3 and then the formula in A11 is (=+B3) then in A10 I put (=+A11-1) then in A9 I put (=+A10-1) and do this up through A5.

I am trying to create it so that when it is used, all a person has to do is put a date in B3 and it will automatically calculate the dates for A5 though A11. Since this is a monthly log there are typically 4 to 5 worksheets and using the example above, I would want that worksheet to show "Week Ending 12 October 2012", and then of course the next worksheet would automatically rename itself acording to the date in B3.

Any help would be greatly appreciated.

Many Thanks
Wes Morgan

See More: How to automatically name a worksheet from the date in a cel

Report •

October 4, 2012 at 17:10:18
✔ Best Answer
Let's start with the easy parts:

First, you have plus signs in all your formulas. That's a throwback to ancient times and is not necessary. Save yourself some keystrokes and don't use them anymore.

Second, you said "I do have codes in cells A5 through A11."

The word "code" is usually reserved for VBA, like the "code" I offer below. It would probably be better (i.e. more easily understood) if you said "I do have formulas in cells A5 through A11."

As for naming your sheets, this Worksheet_Change macro will name the sheet based on the date entered in B3. The Sheet will be named as soon as the date is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$3" Then
     Me.Name = "Week Ending " _
                 & Day(Target) & " " _
                 & MonthName(Month(Target)) & " " _
                 & Year(Target)
  End If
End Sub

Do you have some way of ensuring that the users enter a valid date in B3? Data Validation can take of that by simply using the Date option, but that won't ensure that they enter a valid week ending date. You can use Data Validation to take of that too, but it's a bit more complicated than just choosing the Date option.

Come on back if you'd like some help with that part.

BTW...You could also set it up so that the user only has to enter the week ending date in the first sheet and the other sheets could use a formula to place the other week ending dates in the other B3's.

The VBA code could then be modified so that it names all the sheets as soon as the date is entered in the first B3.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 4, 2012 at 17:45:32
Thank you so much for your reply. I did try the macro but I couldn't get it to work. For example after I pasted it into the module. I went to the worksheet, changed the date to 12/7/2012 and it didn't rename the worksheet. I do have that cell formated so the date would reflect "Friday, December 7, 2012".

I also like the idea of putting the number in the first worksheet of B3 and it calculates all the other worksheets weeks.

Report •

October 4, 2012 at 18:03:14
What module did you paste it into?

A Worksheet Event macro has to be pasted into the Sheet module for the sheet you want it to run in. Right click the sheet tab, choose View Code and paste the code into the pane that opens.

I formatted B3 the same as yours, entered 1/9/2012 and the sheet was renamed to:

Week Ending 9 January 2012

BTW...the formatting should not effect the running of the macro. As long as Excel recognizes the value in the cell as a date, it should work. It it's not a date, you should get an error since there is no error handling written into the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

October 6, 2012 at 16:20:28
That worked PERFECT!!!!! I was pasting the code into a new module that wasn't even for that worksheet. But after you explained how to do it, it was great.

Thank you for all your help.

Many Thanks

Report •

Ask Question