Merge Multiple Excel Workbook Sheets Into 1 Excel File

This VBA code will:

1. Create a new .xlsx workbook
2. Import the first worksheet for all Excel files within a specified directory
3. The final result will have the first worksheet placed in it's own tab

 

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\users\username\Desktop\Directory of Files" 'Change this path accordingly (Files you want imported)
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xlsx", vbNormal) 'Change this extension accordingly (.xlsx, .xls, .txt, etc.)
    If Len(strFilename) = 0 Then Exit Sub

    Do Until strFilename = ""
             Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
             Set wsSrc = wbSrc.Worksheets(1)
             wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
             wbSrc.Close False
             strFilename = Dir()
     Loop

wbDst.Worksheets(1).Delete
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub