MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel macros and VB

Posted by Bob Vanjoff on January 11, 2002 10:38 AM

I am looking for some advice. I have an Excel spreadsheet that I track my team's attendance with. There is a row for every day of the year, and each day has 3 different time entries. My timekeeping system will allow me to generate Excel reports for my teams daily punches. What I want to know is if it is possible to write a macro, or use visual basic to have Excel look at the daily report, find an employees name, copy the total hours from that report and then paste it into the big tracker sheet. I think it boils down to how easy or hard would it be? Thank you very much in advance for any advice anyone may have.

Posted by George VanDolay on January 11, 2002 11:21 AM

Bob - Something's not clear. You have three columns, with each line (ie, day) having three time entries.

1. Are the time entries durations of time, or actual clock time? (If the entries are clock times, then George reserves the right to more questions.)

2. Where are the team names located? Do you have different sheets for different teams?

George is sure that the answer is yes, a macro could do what you want, but you'll have to explain a bit, so that your new audience can respond appropriately.


Posted by Bob Vanjoff on January 11, 2002 11:39 AM

Time durations are minutes and seconds. The tracker is laid out thusly: col. 1 date;col 2 time started; col 3 time finished; col 4 total phone time; col 5 total AUX code time; col 6 total TKS (this is the system we use for timekeeping) time; there are a couple of other columns for other tracking. The second spreadsheet generated by the TKS system would be setup thusly: there are 4 codes I=in time C=change (coffee breaks) L=lunch R=retrun from lunch O=out time. At the bottom of the report would be the total TKS time. Essentially, I'm thinking that I can get Excel to look at the daily report and do this (roughly :) like I said, it's been a while since I did this level of VB or macros) Look at the daily report>find the first agents name>look at the total TKS time for that person>copy it to the appropriate days row on the main sheet. Hopefully, that will help. If need be, I can provide you with a small sample of both reports tomorrow when I'm back at work. Thanks for your help!

Posted by George VanDolay on January 11, 2002 4:34 PM

Bob -
You've edged a bit further than asking VBA advice. You've got a project. Aand George just got a headache.

Anybody got a free week-end, here.?