Help with macros for employee punch clock

[rageo]

Hi,
I'm fairly new to excel and macros, and am currently in the process of designing some sort of employee scheduling worksheet to help increase the efficiency for my dad's restaurant.

I currently have two separate worksheets, one with the schedule and one which does the punch in and punch out (I used the very basic macro recording and assigned it to a button for ease of use). For now, I have to manually transfer the information from the punch in worksheet into the worksheet with the schedule:

<table style="border-collapse: collapse; width: 1067px; height: 177px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 61pt;" width="81"> <col style="width: 38pt;" width="51"> <col style="width: 41pt;" width="54"> <col style="width: 43pt;" width="57"> <col style="width: 41pt;" width="54"> <col style="width: 38pt;" width="50"> <col style="width: 39pt;" width="52"> <col style="width: 42pt;" width="56"> <col style="width: 44pt;" width="58"> <col style="width: 38pt;" width="51"> <col style="width: 39pt;" width="52"> <col style="width: 38pt;" width="51"> <col style="width: 38pt;" width="50"> <col style="width: 38pt;" width="51"> <col style="width: 39pt;" width="52"> <tbody><tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt; width: 61pt;" width="81" height="18"> <table style="border-collapse: collapse; width: 617pt;" width="820" border="0" cellpadding="0" cellspacing="0"><col style="width: 61pt;" width="81"> <col style="width: 38pt;" width="51"> <col style="width: 41pt;" width="54"> <col style="width: 43pt;" width="57"> <col style="width: 41pt;" width="54"> <col style="width: 38pt;" width="50"> <col style="width: 39pt;" width="52"> <col style="width: 42pt;" width="56"> <col style="width: 44pt;" width="58"> <col style="width: 38pt;" width="51"> <col style="width: 39pt;" width="52"> <col style="width: 38pt;" width="51"> <col style="width: 38pt;" width="50"> <col style="width: 38pt;" width="51"> <col style="width: 39pt;" width="52"> <tbody><tr style="height: 16.5pt;" height="22"> <td class="xl65" style="height: 16.5pt; width: 61pt;" width="81" height="22">
</td> <td colspan="2" class="xl72" style="width: 79pt;" width="105">25/3/2009</td> <td colspan="2" class="xl72" style="width: 84pt;" width="111">26/3/2009</td> <td colspan="2" class="xl72" style="width: 77pt;" width="102">27/3/2009</td> <td colspan="2" class="xl72" style="width: 86pt;" width="114">28/3/2009</td> <td colspan="2" class="xl72" style="width: 77pt;" width="103">29/3/2009</td> <td colspan="2" class="xl72" style="width: 76pt;" width="101">30/3/2009</td> <td colspan="2" class="xl72" style="width: 77pt;" width="103">31/3/2009</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl65" style="height: 13.5pt;" height="18">
</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> <td class="xl75">Time In </td> <td class="xl75">Time out</td> </tr> <tr style="height: 29.25pt;" height="39"> <td class="xl76" style="height: 29.25pt;" height="39">Alice</td> <td class="xl66" style="border-left: medium none;" align="right">2:00 PM</td> <td class="xl67">3:00 PM</td> <td class="xl69">
</td> <td class="xl67">
</td> <td class="xl66">
</td> <td class="xl70">
</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl76" style="height: 30pt;" height="40">Ben</td> <td class="xl66" style="border-left: medium none;">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> <td class="xl66">
</td> <td class="xl70">
</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> </tr> <tr style="height: 30pt;" height="40"> <td class="xl76" style="height: 30pt;" height="40">Charley</td> <td class="xl66" style="border-left: medium none;">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> <td class="xl66">
</td> <td class="xl70">
</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl66">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> <td class="xl69">
</td> <td class="xl67">
</td> </tr> </tbody></table></td> <td class="xl72" style="width: 38pt;" width="51">
</td> <td class="xl72" style="width: 41pt;" width="54">
</td> <td class="xl72" style="width: 43pt;" width="57">
</td> <td class="xl72" style="width: 41pt;" width="54">
</td> <td class="xl72" style="width: 38pt;" width="50">
</td> <td class="xl72" style="width: 39pt;" width="52">
</td> <td class="xl72" style="width: 42pt;" width="56">
</td> <td class="xl72" style="width: 44pt;" width="58">
</td> </tr> </tbody></table>

I'm having difficulty trying to combine the 2 processes together so I dont have manually input the clock in/out times.

Could anyone tell me how I can "select" the empty cell matching the employee name and todays date, so i can paste in the clock in timings?

Thanks in advance for your help.

For reference, my clockin macro looks smth like this:

Calculate
Range("D5").Select
Selection.Copy
Range("D15").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("C8").Select
Selection.Copy
Range("C15").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[VoG]

Hello and welcome to MrExcel.

It would help if you could explain what that code is supposed to do. Also please post shots of your clock in/out sheet and schedule sheet using Excel Jeanie

 

[rageo]

The macro above basically just copies the employee name and the time he comes in/ goes off work and puts the info into a table like this (i shifted the positions of some of the cells to make it more compact):

Excel Workbook
	C	D	E
13	Employee Name	Date	Time
14	Alex	15/3/2009	4:39 AM
15	Name	Clock In	Clock out
16	Linda	4:28 AM
17	Alex	4:34 AM
18	Alex	4:37 AM
Sheet1

Currently, the clock in/ out is done by the supervisor on duty, I consolidate the times into the schedule sheet and calculate the payroll at the end of the day.

The schedule sheet looks something like this

Excel Workbook
	A	B	C	D	F	G	I	J	L	M	O	P	R	S	U	V	X	Y
10			25/3/2009		26/3/2009		27/3/2009		28/3/2009		29/3/2009		30/3/2009		31/3/2009		TOTAL
11	hourly pay		Time In	Time out	Time In	Time out	Time In	Time out	Time In	Time out	Time In	Time out	Time In	Time out	Time In	Time out		Est. Cost
12	$7.50	Alice	2:00 PM	3:00 PM													1.00	$7.50
13	$6.50	Ben															0.00	$0.00
14	$18.25	Charley															0.00	$0.00
Sample

 

[rageo]

I think all i need is the macro coding to select the empty cell matching 2 conditions, which in this case would be the date and the employee name.

Can anyone help me with what macros i could use?