Copy range from multiple sheets with same name in files in same folder to single sheet

Hi all,

I have problem with a VBA code. I bet it’s a stupid mistake I’m doing but cannot figure it out.
I have folder with many excel files (Document Folder). In each of the excel files I have sheets, and one of them is named “Orders”.
I need to copy the values (e.g. range A1:X100) from the sheet “Orders” from all of the excel files in a single “MasterData” sheet.
In the column “A” of the “MasterData” sheet I need to have the excel file name.
In columns C and further right I need to have the data from sheet “Orders”, ideally just a text values (I don’t need to see the formulas in the copied cells, rather the result). It will be really great if I could have also the formatting for the copied cells.

I’ve made a code that is not working as I wish. Can anyone help me figuring out where my mistake is.

Please Login or Register  to view this content.

Thank you in advance.
Igor

Hi Banaanas,

Thank you a lot for your help. The copying works!!!

The only thing is that it overwriting the copied text. I've made a small change to copy each file below the previous one. I've made small change in the code to copy 50 rows after the previous one. I tis not ideal solution, but for the task i need it will do.

Please Login or Register  to view this content.

its not working for me.
I got " Run time error "9":
Subscript out of range