Excel 2003 - importing info from closed workbooks
 

I am hoping to automate the extraction of information from a series of
workbooks submitted by staff each week into a main spreadsheet.

The importing information is laid out exactly in the same columns as
the main spreadsheet but I do not know how many rows of information
they may have input.

What I would like to do is open the main spreadsheet, press a macro/
button that opens every file in a sub directory finds out how many
rows of information there and then copy that info to the main
spreadsheet, close the worksheet and then open the next one in the sub
directory and repeat the process.

Can anyone point me in the right direction of any suitable VBA coding
that would give me a starting point

Thanks for your help

BJthebear
Scotland