How can I Merge Worksheets from different Excel Files into one Worksheet?

[Jenny]

I would like to merge worksheets from a number of different excel
files into one giant worksheet.

Every week I need to copy one specific worksheet from each workbook
(approx 200) into one worksheet. The workbooks have up to 5 worksheets
but this specific worksheet has the same name in each workbook.

I have all the files, and only those, in one folder per week.

All the worksheets have the exact same columns and they all have data
starting in row 8. Rows 1-7 are either empty or they have a column
header.

The data can be up to 5000 rows or only 1 row.

I use Excel 2002 and would like to do this using VBA but I don't know
how to write the macro.

Thanks in advance.

 

[Ron de Bruin]

Try this example Jenny
http://www.rondebruin.nl/tips.htm