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

Does this fit your needs?

Please Login or Register  to view this content.

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