multiple workbooks, multiple sheets, all same layout. scan all into master workbook

[tlgdesign]

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,

[alansidman]

Hi and welcome to the forum

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome (manually entered is ok) and how you arrived at that. (Exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you. Also, not all members can upload picture files (Company firewalls, etc.)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[tlgdesign]

Hi,

Please see the file attached.

receptionist-a.xlsx

that is an example of a file submitted by an employee.

there are several such workbooks.

I want to have a master book, which will read column a (expense category) on each sheet of each workbook and copy the whole row if it meets a criteria(similar to sumif. However, i am looking not just for the sum, but the whole row to be copied) to the appropriate sheet (each category will represent a sheet on the masterbook).