I have 7 employees who keep similar lists in Excel. Each list has the same four items in it (columns A through D). Column C is the date. There are anywhere from 0 to 100 line items entered into the list for each date.
I regularly open all seven worksheets, copy the NEW data, and paste it into another worksheet that has a combined list from all 7 people. I use the combined list to feed a pivot table.
I would like to automate this task... I'm open to suggestions on how to solve this...
A macro to open each file in turn, copy the new data, paste it at the bottom of the list is the only thing I've been able to think of. I'm not sure the best way to do it as I would want to specify for the macro to ONLY grab the data with a specific date in each of the 7 "child" worksheets to copy them into the "parent" worksheet.
If there is another way to accomplish this without using a macro, that's good too. Any and all suggestions are welcome.
Thanks in advance!