Populating Timsheet template from Excel Data Source

[vram]

Hello,

I work for a company that has several employees clocking in and out electronically from home. All this information gets logged into our system which then generates an excel file with fields such as member name, date, task they are working, pay period, and clock in and clock out times. All of this information is logged into one file so this report contains several hundred members. I am trying to create timesheets from this source data for each member. I have already created an excel template and was wondering if there is a macro that can create populate this timesheet template several hundred times for all of our members? I know it might be a stretch and not possible. As of now, my course of action involves creating a 'concatenate' column to combine my members with their task and pay period. A timesheet gets created per member, per task, so if they have more than one task, they would need a second timesheet for those dates of service. I then sort by this column to group all of my lines together that would in theory be one timesheet. I did a concatenate of my member's internal number and task to create a shorter concatenate column. I then did a pivot on this second concatenate column to get a 'timesheet list' into one sheet and ran a macro to create a new sheet for each cell value which was also named based on the cell value. I was then planning on pasting my template into each of these tabs and would then try to find a way to bring the information from my source sheet into each individual sheet based on matching criteria, so an index formula possibly? If anyone can think of a faster way to go about this process, I would greatly appreciate it!

Thank you,
V

 

[GSP75]

Yes, it is possible to create a macro that populates your timesheet template for all of your members. Here's a potential workflow that you could follow:

1. Start with your source data sheet and create a unique list of member names in a new sheet. You can do this by selecting the column with member names, going to the Data tab, and selecting "Remove Duplicates".
2. In the same new sheet, create a column for each task that a member might have. For example, if a member can work on Task A and Task B, create two columns named "Task A" and "Task B".
3. In each column, use the VLOOKUP function to populate the start and end times for each task. For example, in the "Task A" column, you might have a formula like "=VLOOKUP(A2&"Task A",SourceData!$A$2
4. Copy
5. Use
6. In