Open multiple workbooks based on cell values and copy and paste information

Hi,

I am really sorry that I am asking a question without making any progress on that but I really couldn't find anything on this or the methods I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.

What I am trying to do is that;

1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks

2.) And macro can pull the information from related files stored in a certain folder

It is very much like another members problem but I am not sure why I cant get that code working for me?

http://www.ozgrid.com/forum/showthread.php?t=158145I

just spent hours on this and I am sure for a pro it would have an easy answer.

I attach the workbook exactly explains what I am trying to achieve.

I would really appreciate any help.

Thanks in Advance.

Egemen

P.S. Also posted the same question on another forum and in case I get an answer there I will update it here

http://www.mrexcel.com/forum/excel-q...ml#post3785429
http://www.ozgrid.com/forum/showthre...d=1#post710760