Intricate Paste/Transpose VBA

I have included several sample documents to help explain what I need. Hopefully this doesn't get too confusing:

I am trying to create a document that will automatically count details of employees' time sheets.

I am quite new to VBA and as I try to figure this out it seems to get more and more complicated!

Theoretically: an employee would turn in a time sheet in the form of an excel file (all time sheets will be formatted the same way, we have a time sheet template). The most important information for my needs are the Hours of Absence and the Absent Code.

I need to take the information from each time sheet (time sheets cover 2 weeks) and put it in a document (Attendance Document) that calculates all absences for the entire year. The document will have a seperate tab for each quarter.

On the time sheets, Hours of Absence and Absent Codes are recorded in two different columns. In the Attendance Document these two pieces of information need to be in the same cell.

While the time sheets have the data in a vertical format (Column Headings are Day, Date, Hours of Absence, Absent Code, Hours Worked) The Attendance Document (which will hold information for ALL employees) will follow a more horizontal format (column headings are Employee Name, Then Several Headings for Totals, and then a Heading for each day of the quarter).



I guess I don't even know if it is possible to create one formula for all that i need but I would really appreciate any input!!!!

Again, hopefully these samples make things a little more clear. the "Attendance_Document" shows an example of what I need the end result to look like and a blank table to use as a destination. The "Time Sheet" files are samples of my sources. The colors are somewhat irrelevant and don't necessarily need to be copied, they are there mostly to help keep things straight!

Attendance_Document.xls

TIMESHEET1.xls

TIMESHEET3.xls

TIMESHEET4.xls

TIMESHEET2.xls

Hi

Put the attached file and the example workbooks all in the same directory. Open this workbook, and run the code. Output should be put in sheet2.

I've not put in the formulas, just tried to bring in the data. Make sure that is all being brought in as required.

rylo

Thank you for all your work but, I can't get the code to work with your example. I don't quite know what I am doing wrong. When I run the Macro (aaa), nothing happens.

Hi

1) Put all 5 example files in a directory on their own.
2) Replace attendance document with the one from my zip file.
3) Open attendance document from the directory, and run the code.

If that doesn't work, then give the exact steps that you are taking and advise any error messages that you are getting.

rylo