Hi there,
I'm need of some help please, as I'm currently having to track and compile all the data mentioned below manually.
Here is the situation:
I have a workbook with several sheets. Each sheet contains a weeks worth of punch times for several employees. The data on each sheet is always formatted the same. This is what it contains:
[TABLE="width: 830"]
<tbody>[TR]
[TD]Div[/TD]
[TD]Dept[/TD]
[TD]Manager[/TD]
[TD]Employee[/TD]
[TD]ID[/TD]
[TD]Punch Type[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock In[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]13:23[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock Out[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]19:11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Clock In[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]06:29[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Going to Meal[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]12:33[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Back From Meal[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]13:31[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Clock Out[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]15:32[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock In[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]11:30[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Going to Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]17:26[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Back From Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]18:21[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock Out[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]20:34[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Clock In[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]11:34[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Going to Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]14:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Back From Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]15:06[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Clock Out[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]20:28[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Clock In[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]11:27[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Going to Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]16:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Back From Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]17:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Clock Out[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]20:27[/TD]
[/TR]
</tbody>[/TABLE]
Ideally I would like to have the macro look for each name on the employee column, the day, date, and punch type, then paste the clock in, meals, and clock out onto another workbook on specific cells. This is what the other workbook looks like:
[TABLE="width: 637"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Schedule[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day of Week[/TD]
[TD]Date[/TD]
[TD]Clock In[/TD]
[TD]Lunch Break Taken[/TD]
[TD]Clock Out[/TD]
[TD]Scheduled Time In[/TD]
[TD]Lunch Break[/TD]
[TD]Scheduled Time Out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]6/9/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]6/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]6/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]6/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]6/13/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]6/14/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]6/15/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
On this second workbook, I have a sheet for each employee. Each sheet contains the data above for a whole quarter. From here, I have formulas that give me the difference between the clock in, lunch break, clock out versus what the employee is schedule.
Sorry, I know is a lot and probably super confusing. Any thoughts, ideas, suggestions will be greatly appreciated.
I'm need of some help please, as I'm currently having to track and compile all the data mentioned below manually.
Here is the situation:
I have a workbook with several sheets. Each sheet contains a weeks worth of punch times for several employees. The data on each sheet is always formatted the same. This is what it contains:
[TABLE="width: 830"]
<tbody>[TR]
[TD]Div[/TD]
[TD]Dept[/TD]
[TD]Manager[/TD]
[TD]Employee[/TD]
[TD]ID[/TD]
[TD]Punch Type[/TD]
[TD]Day[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock In[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]13:23[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock Out[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]19:11[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Clock In[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]06:29[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Going to Meal[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]12:33[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Back From Meal[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]13:31[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]JONES, JAMES[/TD]
[TD]JOHNSON, PAUL[/TD]
[TD="align: right"]2345[/TD]
[TD]Clock Out[/TD]
[TD]Sat[/TD]
[TD="align: right"]06/09/2018[/TD]
[TD="align: right"]15:32[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock In[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]11:30[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Going to Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]17:26[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Back From Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]18:21[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]SMITH, JOE[/TD]
[TD="align: right"]1234[/TD]
[TD]Clock Out[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]20:34[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Clock In[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]11:34[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Going to Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]14:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Back From Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]15:06[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]SMITH, AMBER[/TD]
[TD]GARCIA, JOSE[/TD]
[TD="align: right"]3456[/TD]
[TD]Clock Out[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]20:28[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Clock In[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]11:27[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Going to Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]16:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Back From Meal[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]17:05[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]9999[/TD]
[TD]PETERS, JOHN[/TD]
[TD]PAUL, ROBERT[/TD]
[TD="align: right"]4567[/TD]
[TD]Clock Out[/TD]
[TD]Sun[/TD]
[TD="align: right"]06/10/2018[/TD]
[TD="align: right"]20:27[/TD]
[/TR]
</tbody>[/TABLE]
Ideally I would like to have the macro look for each name on the employee column, the day, date, and punch type, then paste the clock in, meals, and clock out onto another workbook on specific cells. This is what the other workbook looks like:
[TABLE="width: 637"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]Schedule[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day of Week[/TD]
[TD]Date[/TD]
[TD]Clock In[/TD]
[TD]Lunch Break Taken[/TD]
[TD]Clock Out[/TD]
[TD]Scheduled Time In[/TD]
[TD]Lunch Break[/TD]
[TD]Scheduled Time Out[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday[/TD]
[TD]6/9/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunday[/TD]
[TD]6/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]6/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]6/12/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]6/13/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday[/TD]
[TD]6/14/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday[/TD]
[TD]6/15/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:30:00[/TD]
[TD]1:00:00[/TD]
[TD]15:30:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
On this second workbook, I have a sheet for each employee. Each sheet contains the data above for a whole quarter. From here, I have formulas that give me the difference between the clock in, lunch break, clock out versus what the employee is schedule.
Sorry, I know is a lot and probably super confusing. Any thoughts, ideas, suggestions will be greatly appreciated.