How to consolidate data from different files?

[Khoshravan]

I have a workbook for product sales data. each sheet in the workbook
contains data for different product and inside each sheet, salespersons and
their data exists.
I send this file to all of my sales persons, they filter the sheets to their
own name, enter relevant data and return it back to me by email. Now I have
many files.

Now I have to consolidate the returned files to make a single file and send
it to my boss.

First q) How can I consolidate these files?

Second Q) It seams that my original table design is not good. It would be
better to separate salespersons to different

sheets. This way it is easier to consolidate.

 

[Dave Peterson]

If the sales persons remove the other people from their worksheets, then you
could recombine all the data into one worksheet using code from Ron de Bruin.

http://www.rondebruin.nl/tips.htm
Look for Copy/Paste/Merge examples
and you'll see lots to review to see if they fit.

If they don't remove the data (they just hide it with filters), then I'd clean
that up before doing the merge code in Ron's samples.

I think the only difference I'd have is to send each sales person just the rows
of data that belongs to them.

Ron has some nice code for that kind of thing, too.

You may want to take a look at his easyfilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or even his SendMail addin.
http://www.rondebruin.nl/mail/add-in.htm

He's got lots more addins here:
http://www.rondebruin.nl/addins.htm

 

[Khoshravan]

Dear Dave

Thanks for your reply. let me check and reply later.

I know it is a better way to send to every salesperson only the part s/he
has to fill, but in my case it is not that way.