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
Bookmarks