Collating data from varying number of sheets

Hi ppl,

Right I am loading data in to seperate sheets in to excel, with the sheet names being the file names where the data came from (each file has a sheet within called "dump" which collates all data needing to be reviewed and is then transferred over to my "analysis" workbook). The number of sheets will vary as will the names of the sheets.

The sheets follow a general format in that their names are made up of the team they come from and also the week commencing date the data was recorded for e.g.

FR1-23.11.2009
RA12-23.11.2009
Etc for a possible 24 teams.


I want to carry out analysis on the data listed in these files or, to at least collate the data in to more meaningful tables in a new sheet. I wish to have all of this automated in some way so when sheets are loaded a table can begin to be populated.

What is the best way of doing this and how? Once the general table containing data from all sheets is produced I will just simply carry out my further calculations from this.

Thank you for any help and ideas.