Combine data from multiple worksheets into one.

Every month several employees fill out a form that contain all their expenses for the month.

Expense Detail - Category - Expense Amount

I want to be able to take each form and import it into a master file.

The file would contain a master worksheet that would contain all the tables for each expense report merged into one table.

If John's expense sheet contained 10 expenses (rows of data)
and Mary's contained 17
and so on..

I want mastersheet to list John's expenses; 10 rows (a2-a12). And then list Mary's expenses starting where John left off (a13 - a30)

I want to be able to analyze the data afterwords, but i'm currently having trouble compiling it in one sheet automatically.

This can be done via copy/paste, but i want it to be automatic.

That means if i were to add something to Mary's list, it would automatically compile in mastersheet.

Hi Paresh, Tks for the quick reply.

its a little more complicated than that.

I will explain in the next post

Hey Richard,

I'm sorry but my last post wasn't very clear. I'll try to explain it better.

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,

All the workbooks will be in one directory

Is this macro already available or do I have to make it?

Also is there a function similar to sumif, that, instead of just simply adding sum if a criteria is met, it will copy the entire row if criteria is met?