Need help pulling data from several identical workbooks and combining into one sheet.

Can someone help me pull data from several workbooks with this form in it? Each employee will fill out their section, so there isn't any overlap. I can't have a shared workbook. I have that now and it causes issues upon issues and is a CF.

I have other sheets I need to do like this, but I think I should be able to adjust the macro if I have it. I just don't know where to start with pulling from outside and how to do it efficiently.

So needy! lol

Here are two samples. Same thing, but data is in separate fields. It is what I am currently using trying to figure this out. I found a macro online I was trying to make work, but it combines workbooks not worksheets from separate books.

Each is a log of daily activities on an employee. A:E is Employee 1, while F:J is Employee 2. Everything the employee does during the day is logged for tracking. I want to be able to combine the worksheets together so that all my employees end up on one sheet.

I am combining workbooks. Right now my team uses a shared workbook and just enters their activity in their columns. since Employee 1 is A:E, they will never overwrite Employee 2. Here is the catch though. I have a few other worksheets that I will be combining, so I can't just mass combine the workbooks all together. I need sheet 1 of each worksheet combined, then i'll need to combined sheet 2 with the other sheet 2s.

Does that help? The worksheet will always been named Prod Totals. The file name will be employee specific, so either I can alter that to match the name, or if we point to a folder and just combine the contents.

Perfect world. I run my macro and it goes into my folder. It then combines all sheet 1s together, then combines all sheet 2s together, and all sheet 3s together into one workbook. This is step (sheet) one.

I don't know what you tryin' to show with your examples but generally you can use PowerQuery (free add-in for Ex2010 from MS site)

Originally Posted by TMS

Can't help thinking you'd make life a lot easier for yourself if you just had one workbook for each employee in a common folder. Then you could collate the data from all the employee workbooks.

That’s what I am trying to do.

Originally Posted by sandy666

I don't know what you tryin' to show with your examples but generally you can use PowerQuery (free add-in for Ex2010 from MS site)

Examples of what each employee would have. It’s two employees. So you have two workbooks. I then need to combine them into one workbook.

Thanks Sandy, the issue i've discovered with PQ is that it creates a table and creates headings. Like if you look in my sample workbooks. I have headings but they are on row 2 but row 1 has info as well. So creating headings based on row 1 is not correct.

I did it to show example only. This is your job to do it correctly with your own rules

No. i didn't use your example. I did it on my own. PQ isn't going to work if it is going to add headers.

That is why I said you will need reorganize a little your sources data. PQ works on columns and content. This is easiet way what I know in this case.
But if it doesn't work for you, sorry.
Good luck and have a nice day

Thanks. I have other macros tied to this formatting as well. That's why I was hoping for another macro solution. but thanks for taking a look

Another solution is to have each worker have the 'same' worksheet, then combine data via a macro into a consolidated workbook (so for example, if there was 10 Employees, you would have 10 files, one for each employee). The tab name could designate each employee (to differentiate whose did what in Consolidated) OR add another field with their initials/EE number etc. Looks like you are well on your way. Good luck

queuesef, isn't that what my macro is doing, besides the adding another field?

No I agree. I'm sure it can function better, but it is functioning... now my other sheets