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.

Sub GetSheets()
'Folder with files
    Path = "C:\Users\Document Folder\"

'Go throw all excel files in folder and open them
    FileName = Dir(Path & "*.xls*")
        Do While FileName <> ""
        Workbooks.Open FileName:=Path & FileName, ReadOnly:=True

'Find last used cell in "MasterData" sheet
    lastRow = ThisWorkbook.Sheets("MasterData").UsedRange.Row + 2

' Add the open File name in column A
    ThisWorkbook.Sheets("MasterData").Range("A" & lastRow).Value = FileName
' Copy data from range "A1:X100" from open file, sheet "Order" to column "C:Z" in "MasterData" sheet
    ThisWorkbook.Sheets("MasterData").Range("C" & lastRow).Value = FileName.Sheets("Order").Range("A1:Z90").Value

'Close opened file
     Workbooks(FileName).Close
     FileName = Dir()
  Loop
End Sub

Thank you in advance.
Igor

Does this fit your needs?

'Always use option excplicit. You need to declare all variables, it makes the code more robust.
Option Explicit

Sub GetSheets()

    'Declaring the variables to be used
    Dim Path As String
    Dim filename As String
    Dim masterWorkbook As Workbook
    Dim currentWb As Workbook
    Dim lastRow As Long

    'Disable screenupdates making it faster (and opening workbooks will not flash the screen constantly)
    Application.ScreenUpdating = False

    'Disable alerts (Excel asks if you wan't to spare the copied data in memory after closing otherwise)
    Application.DisplayAlerts = False

    Set masterWorkbook = ThisWorkbook

    'Folder with files
    Path = "C:\Users\Document Folder\"

    'Go throw all excel files in folder and open them
    filename = Dir(Path & "*.xls*")
    Do While filename <> ""
        Set currentWb = Application.Workbooks.Open(filename:=Path & filename, ReadOnly:=True)

        'Find last used cell in "MasterData" sheet
        lastRow = masterWorkbook.Sheets("MasterData").UsedRange.Row + 2

        ' Add the open File name in column A
        masterWorkbook.Sheets("MasterData").Range("A" & lastRow).Value = currentWb.Name
    
        ' Copy data from range "A1:X100" from open file, sheet "Order" to column "C:Z" in "MasterData" sheet
        currentWb.Sheets("Order").Range("A1:X100").Copy
        masterWorkbook.Sheets("MasterData").Range("C" & lastRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        'Close opened file
        currentWb.Close
        filename = Dir()
    Loop
  
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

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

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.

        'Find last used cell in "MasterData" sheet
        lastRow = masterWorkbook.Sheets("MasterData").Cells(Rows.Count, 1).End(xlUp).Row + 50