consolidating excel files into one file not using hard code of folder path

hi,
i tried to find a macro to consolidate multiple excel files into one file, in the forum but could not find anything which suits my requirements....
can someone pls help me with this....

i have approximately 30 excel files in a folder, which i should consolidate into one sheet...
the path of the folder is not same...it changes from day to day...
for instance the files which we use today will be saved in today's dated folder i.e., "Day shift 12/19/2010"...
i should consolidated all the files in the above folder at the end of the day...which i should repeat daily...
the excel file contains data only in sheet1...
and rows and columns are not fixed...
each files contains header... and the header should include only ones in master sheet.


your help is greatly appreciated...

hi,

Yes,
the Header should be copied from the first sheet from that folder...
pls find the sample attachments....

Try this option. All files should be in one folder.

hi Guys,

you guys really made my day....
thanks from bottom of my heart...
this forum is really great one......
get back to you if i need any assistance....