Update/Merge data from different workbooks with the same table

Hello, guys,

I've got one table to fill with 30 people. So, I send this table separately, they fill their parts of the table (data in several columns, other data/columns are the same) and send back.
So, in the end, I have 30 files with one table with partly filled columns. In the perfect world, all these 30 parts would have made 1 full table, but it is not.
The mechanism the same with Google sheets, these 30 people would fill the same online table. But I can't do it due to security.
All in all, I need to merge 30 same tables with partly filled several columns that are located in the same workbook. And need to see who did not send me the info (the cells will be empty).

Crying for help! Wasting for this a lot of my time! All ideas appreciated.

Please attach a zip file with 3-4 workbooks. You can replace the actual data with dummy data; it doesn't matter. Without that, it will be very though to help.

This is a quick and dirty way of doing this. I loop all cells in all workbooks and if they are not empty then they will be pasted on the collection workbook.
I am 100% sure that there are a lot more efficient ways of doing this, but I just don't have the time to dig deeper right now

Download attachment or use this code in a workbook:

Please Login or Register  to view this content.

An alternative is using ADO/DAO to retrieve the datas from the related files without openning them.

The below code can be placed in a new workbook and saved as "xlsm" file in the folder which houses the data files.

Please Login or Register  to view this content.