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

[egemencoskun]

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 workbooks2.) And macro can pull the information from related files stored in a certain folderIt is very much like another members problem but I am not sure why I cant get that code working for me?VBA Open Files from a list 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.EgemenP.S. Also posted the same question on another forum and in case I get an answer there I will update it here... Also there is an attachment too..Open multible workbooks based on cell values and copy and paste information

 

[egemencoskun]

UPDATE!!!I got this in another forum and does lots of what I wanted.WBs to Sheets - Jerry Beaucaire's - Excel Assistant - Workbooks to Sheets - PART 2And my replyYour code would definitely work for me. Just tested that and does exactly what I needed apart from all my files will be password protected so it needs to somehow enter the password to open workbooks and also it seems like it keeps running. Unless I press and hold esc key the code dont stop running. Not sure but might be due to Loop process? as when I stop the code it just highlights a line before Loop in debug mode.I havent tried it for workbooks with multible tabs.

 

[egemencoskun]

I have no idea why all my posts looses formatting and gets squashed all the time. And I know its a bit difficult to read... Sorry

 

[egemencoskun]

I will close this and will try to come up with code