merge multiple excel files into one sheet vba

[spudn56]

I am stuck and do not know what I am doing wrong. I am trying to merge .xlsx files on daily basis. I have a folder on my desktop called mergeFiles within it there are 3 testfile: test1.xlsx, test2.xlsx and test3.xlsx I usually have around 100 files or more.

[CODE] Sub GetSheets() [CODE]
[CODE] Path = “HD:Users:spudn56:Desktop:mergeFiles.xlsm" [CODE]
[CODE] Filename = Dir(Path & "*.xls") [CODE]
[CODE] Do While Filename <> "" [CODE]
[CODE] Workbooks.Open Filename:=Path & Filename, ReadOnly:=True [CODE]
[CODE] For Each Sheet In ActiveWorkbook.Sheets [CODE]
[CODE] Sheet.Copy After:=ThisWorkbook.Sheets(1) [CODE]
[CODE] Next Sheet [CODE]
[CODE] Workbooks(Filename).Close [CODE]
[CODE] Filename = Dir() [CODE]
[CODE] Loop [CODE]
[CODE] End Sub [CODE]

[Marc L]

Use the icon for the code tags …

And just use the appropriate file extension in your code as .xls is not .xlsx ‼

[spudn56]

I corrected the files to .xls but code still didn't work.