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