Every month I get an email from all employees that have expenses to claim.
I also get emails from the receptionists, who are in charge of the petty cash boxes.

As an example receptionist-a would send me an excel file at the end of every month that looks like this

feb14pc.JPG

Upon receiving it, I would import the sheet it into Receptionist-a.xls

Reception-a.xls would have - to date - 5 sheets (one for every month) ; Jan14 PC, Feb14 PC... May14 PC

every sheet is the same layout

Receptionist b would do the same (receptionist-b.xls)
Any employees with expenses to claim would do the same (john.xls, sara.xls, etc.)

So, as of now:
1. I have 7 workbooks. Each workbook belongs to an employee
2. Each book contains sheets that pertain to a specific month in a specified format. for example, John.xls contains Feb14 John, Mar14 John etc..
3. Column D of each sheet contains a category of expense (as seen in above image). ie. Transportation, Food, Maintenance, etc.

I wish to create a master book. This book will contain a sheet for each category. For example it will have 3 sheets; transportation, maintenance, & food:

The first sheet [transportation] will scan receptionist-a.xls, the first sheet [Jan14 PC].

This is the part i am having trouble with:

If b2 = "transportation" on sheet [Jan14 PC] then it will copy the entire row onto the first available row on sheet [transportation], on masterbook.xls.
it will continue scanning until it completes the sheet.
Next it will do the same for the next sheet [Feb14 PC] and next [Mar14 PC].. and so on until it completes the workbook.

Once it is done scanning all sheets in the workbook, it will do the same for the next workbook until it completes all workbooks.

Upon completion of scanning all transportation expenses and inputting them in the master workbook, it will move onto the next sheet [maintenance] and do the same, and then [food] until all are complete.

The end result being all transportation related expenses claimed by all employees are gathered into one sheet [transportation].
all maintenance related expenses from all employees are gathered into [maintenance] and so on.
Basically, I get a breakdown of all expenses by category

If I haven't explained it well enough, please let me know.

If you think this is a lot harder than I am making it to be, or you don't have the time to explain what I need, then even pointing me in the right direction is well appreciated.

looking forward,