Timesheet code

I am making a timesheet, and for each month I need a separate sheet with the date running down column A. So in the first sheet (Called January) A2- A32 will be 1/1/06 - 31/1/06. When I copy this sheet and rename it February, I would like it to show the dates for this month automatically, any way of doing this? Thanks all!

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

This assumes that the year of interest is entered in cell B1, A1 is available for the month name,
and A2:A32 is formatted for dates.

Change the sheet tab name, select a cell on the sheet, and then slect another cell to fire the
event.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myYear As Integer
Dim myMonth As Integer
Dim myDay As Integer

If Range("$A$1").Value = ActiveSheet.Name Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Range("$A$1").Value = ActiveSheet.Name
Range("A2:A32").ClearContents
myYear = Range("B1").Value
myMonth = Month(DateValue(ActiveSheet.Name & " 1, " & myYear))
myDay = Day(DateSerial(myYear, myMonth + 1, 0))
Range("A2:A" & myDay + 1).FormulaR1C1 = _
"=DATEVALUE(R1C1& "" "" & Row() - 1 & "", " & myYear & """)"
Range("A2:A" & myDAys + 1).Value = Range("A2:A" & myDAys + 1).Value

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

End Sub

"ChrisMattock" <[email protected]> wrote in message
news:[email protected]...
>
> I am making a timesheet, and for each month I need a separate sheet with
> the date running down column A. So in the first sheet (Called January)
> A2- A32 will be 1/1/06 - 31/1/06. When I copy this sheet and rename it
> February, I would like it to show the dates for this month
> automatically, any way of doing this? Thanks all!
>
>
> --
> ChrisMattock
> ------------------------------------------------------------------------
> ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
> View this thread: http://www.excelforum.com/showthread...hreadid=558817
>

Well it adjusts for the Month, i.e. February had 28 days in it, but each day is being displayed with an error, the top date being #VALUE! any suggestions?

Did you put a valid year number in cell B1?

--
HTH,
Bernie
MS Excel MVP


"ChrisMattock" <[email protected]> wrote in message
news:[email protected]...
>
> Well it adjusts for the Month, i.e. February had 28 days in it, but each
> day is being displayed with an error, the top date being #VALUE! any
> suggestions?
>
>
> --
> ChrisMattock
> ------------------------------------------------------------------------
> ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
> View this thread: http://www.excelforum.com/showthread...hreadid=558817
>

Yes I have done...

=DATEVALUE($A$1& " " & ROW() - 1 & ", 2006") is the result that shows in the formula bar, but and error appears in the cell.

Chris,

It might be a regional date setting...

Does

=DATEVALUE("January 1, 2006")

produce an error?


If so, what are valid strings that DATEVALUE will work with on your computer?

HTH,
Bernie
MS Excel MVP


"ChrisMattock" <[email protected]> wrote in message
news:[email protected]...
>
> Yes I have done...
>
> =DATEVALUE($A$1& " " & ROW() - 1 & ", 2006") is the result that shows
> in the formula bar, but and error appears in the cell.
>
>
> --
> ChrisMattock
> ------------------------------------------------------------------------
> ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
> View this thread: http://www.excelforum.com/showthread...hreadid=558817
>