TIME SHEET FORM..??

Posted by colleen on December 20, 2001 8:53 AM

I make a time sheet every month, currently I set up the days and (by looking at a calender) give each day it's appropriate date. (days in cell a1..down to a31 and dates in cell b1..down ro b31) and then enter a name in cell c1...print it out then change the name again before printing it out again, over 28 times. Is there a simpler way that I can make a time sheet for each month and then keep a record of the names in another part of the worksheet, so that each time i want to print for a month, it will atomatically print the time sheet 28 times with the co-workers name entered.

Posted by Tom Dickinson on December 20, 2001 10:53 AM

Ok, I think I've got it so all you have to do is start the macro. In cell A1 put the following:

=TEXT(B1,"ddd")

This will give you the day of the week. Put in a fourth "d" if you want the unabreviated version. Copy this down through cell A31.

In cell D1 enter the month (numerically). Assuming you want January's printouts, enter a 12 for now. (I'll explain later) In cell D2 enter the year (under the same assumption as for D1, enter 2001)

In B1 enter: =DATE(D2,D1,1)
In B2 enter:

Posted by Tom Dickinson on December 20, 2001 11:02 AM

Ok, I think I've got it so all you have to do is start the macro. In cell A1 put the following:

=TEXT(B1,"ddd")

This will give you the day of the week. Put in a fourth "d" if you want the unabreviated version. Copy this down through cell A31.

In cell D1 enter the month (numerically). Assuming you want January's printouts, enter a 12 for now. (I'll explain later) In cell D2 enter the year (under the same assumption as for D1, enter 2001)

In B1 enter: =DATE(D2,D1,1)
In B2 enter: =B1+1
Copy B2 down through B28

In B29 enter: =IF(B28="","",IF(DAY(B28+1)=1,"",B28+1))
Copy this down through B31

In cell E1 and going down, enter the names of the people you want to have listed on the timesheets.

Now for the macro:

Sub TimeSheets()
Dim Cntr As Integer

If Range("D1") = 12 Then
Range("D1") = 1
Range("D2") = Range("D2") + 1
Else
Range("D1") = Range("D1") + 1
End If

Cntr = 1
Do While Range("E" & Cntr) &LT;&GT; Empty
Range("C1") = Range("E" & Cntr)
Range("A1:C31").PrintOut Copies:=1
Cntr = Cntr + 1
Loop

End Sub

The IF/THEN will increment the date by 1 month. (The date calculations on the sheet will automatically take care of the rest.) The do loop will put a name into Cell C1 and then print the area. It will keep doing that until it runs out of names.

Hope this helps.