Timesheet

C

ChrisMattock

I am making a timesheet, and for each month I need a separate sheet wit
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 i
February, I would like it to show the dates for this mont
automatically, any way of doing this? Thanks all
 
B

Bernie Deitrick

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
 
C

ChrisMattock

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?
 
C

ChrisMattock

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

Bernie Deitrick

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
 
C

ChrisMattock

No that doesn't work, here are some that do... (thanks a lot for all
this)


=DATEVALUE("8/22/2008")
=DATEVALUE("22-AUG-2008")
=DATEVALUE("2008/02/23")
=DATEVALUE("5-JUL")
 
B

Bernie Deitrick

Chris,

Try changing

Range("A2:A" & myDay + 1).FormulaR1C1 = _
"=DATEVALUE(R1C1& "" "" & Row() - 1 & "", " & myYear & """)"


to

Range("A2:A" & myDay + 1).FormulaR1C1 = _
"=DATEVALUE(Row() - 1 & "" "" & R1C1 & "" " & myYear & """)"



HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

OR (since I forgot the dashes....)

Range("A2:A" & myDay + 1).FormulaR1C1 = _
"=DATEVALUE(Row() - 1 & ""-"" & R1C1 & ""-" & myYear & """)"

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
Chris,

Try changing

Range("A2:A" & myDay + 1).FormulaR1C1 = _
"=DATEVALUE(R1C1& "" "" & Row() - 1 & "", " & myYear & """)"


to

Range("A2:A" & myDay + 1).FormulaR1C1 = _
"=DATEVALUE(Row() - 1 & "" "" & R1C1 & "" " & myYear & """)"



HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top