Macro to extract data from 60files and copy into one newfile

Question

Good day,

I am new tomacros. I have approximately 60 files (with the same template form) updated every week where I need to extract information from each file for the designer's name in cell G6 and drawing # from cell A11 to A??, whatever the number of entries (varies but the last cell is A40) and % complete from cell F11 to F??, whatever the number of entries. Some times there may be more than one sheet in a file because of the overflowing number of entries. Do I need to have a common sheet name on all 60 files?

I need to extract the aforementioned data from 60 files into one newfile into a table with 1st column containing drawing #, 2nd column with % complete, 3rd column with designer's name (same for all entries within the same file). There are 60 designers, hench the 60 files.

How can I do this with a macro in Excel?

Thank you so much for your help. I need to come up with something in the next couple of days for my boss and I don't know where to start.

Answer

I attach a printscreen of how I imagine your spreadsheets. I need first to get a very good idea of how they look before I can answer. However my first feeling is that using VBA is too high fetched for the purpose. There's a basic rule that if you can do it with standardfunctionality stay with this.

I think however some little VBA will be useful.

There's one thing I don't understand about your phrasing of the question. You say "Some times there may be more than one sheet in a file because of the overflowing number of entries." I thought it only went up to row 40 (last cell is A40). And also can you tell me what's in column B to E? The first column: are they numbers or text?

Kind