Merge worksheets

Hi everyone. I have been trying to work out a macro to merge several hundred worksheets into one, but all I have accomplished is transposition of some of the information in rows instead of columns, needless to say I am very new at this.

I have attached two files. One is called "Combined within Target Prices DS" and the other one is called "Merged worksheets". The information in the Target prices DS files is of daily stock prices of companies and each worksheet represents one company. What I am trying to do is merge all the worksheets to one worksheet (Merged worksheets) and reposition the information, as it is shown in the merged file. Company ID found in the Target prices should be written in each cell of the merged file as well as the name of the company under "comnam", respective to the dates of closing stock prices.

At my current level I am not proficient enough to figure out how to do it. Please, if anyone of you have the time and the patience, help me. I would greatly appreciate it!

I am on Office 2007

I was unable to attach the files to the messages, it seems that there are some issues with the function as it didn't upload the files. The two files can be found here: http://www.mediafire.com/?snx1ab99oy4b0zb and http://www.mediafire.com/?d3dexoxtsal00vn