VBA Combine spreadsheets and format

Brand new VBA !!! Can this be done ?
<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1><!-- / icon and title --><!-- message -->HI guys

Kind of a random question and I hope someone can help.

I am in the middle of completing an excercise where 200 odd Excel sheets were sent out to different areas. They are now returning and I wish to compile an Overview spreadsheet.

So I have a folder on my desktop each workbook saved with a different name ( being the customer name!!!) and each with an unspecified number of tabs.

What I would like ( im going to write this in the best english I can !!!)
is

PART 1 For a blank excel workbook ( with this magic macro) go to this folder and pull all the spreadsheets together (regardless of whether one workbook has 5 tabs or another has 18 etc) and copy the data into this Excel workbook.

PART 2 Transponse and combine the data so the row headers become the Column headers , and it looks more like a Log.

PART 3 Each of the workbooks has numerous tabs but the tab names will contain either the words "Customer", "Facility" or "Drawdown". So, on the master Excel workbook there is only 3 tabs (titled as above) so when bringing the data over if the tab in which its coming from says Facility put it into the facility tab and so on.



If anyone can help with any of this all input would be greatly appreciated !!!!

Thanks
Anne
<!-- / message -->