Try to run the code like this:
Sub MergeWorkbooks()
Dim FolderPath As String
Dim File As String
Dim idx As Long
Dim ThisWkb As Workbook
Dim SourceWkb As Workbook
Set ThisWkb = ActiveWorkbook 'it is book1.xlsx
idx = ThisWkb.Sheets.Count
FolderPath = "C:\Users\abc\Downloads\"
File = Dir(FolderPath & "*.xls")
Application.ScreenUpdating = False
Do While File <> ""
Set SourceWkb = Workbooks.Open(Filename:=FolderPath & File)
SourceWkb.Worksheets(1).Copy After:=ThisWkb.Sheets(idx)
idx = idx + 1
ThisWkb.Sheets(idx).Name = Replace(File, ".xls", "")
SourceWkb.Close False
File = Dir()
DoEvents
Loop
MsgBox "Done"
End Sub
Artik
Bookmarks