Option Explicit

Sub Import_Excel_Files_From_Folder()

    Dim FolderPath As String
    Dim FileName As String
    Dim SourceWB As Workbook
    Dim SourceWS As Worksheet
    Dim DestWS As Worksheet
    
    Dim LastRow As Long
    Dim LastCol As Long
    Dim SourceLastRow As Long
    Dim SourceLastCol As Long
    Dim StartRow As Long
    
    Dim FirstFile As Boolean
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    On Error GoTo ErrorHandler
    
    '----------------------------------------
    ' Active sheet where data will be combined
    '----------------------------------------
    Set DestWS = ActiveSheet
    
    '----------------------------------------
    ' Ask user to select folder
    '----------------------------------------
    With Application.FileDialog(msoFileDialogFolderPicker)
        
        .Title = "Select Folder Containing Excel Files"
        .AllowMultiSelect = False
        
        If .Show <> -1 Then
            MsgBox "No folder selected.", vbExclamation
            GoTo ExitHandler
        End If
        
        FolderPath = .SelectedItems(1) & "\"
        
    End With
    
    '----------------------------------------
    ' First file flag
    '----------------------------------------
    FirstFile = True
    
    '----------------------------------------
    ' Find first Excel file
    '----------------------------------------
    FileName = Dir(FolderPath & "*.xls*")
    
    Do While FileName <> ""
        
        '----------------------------------------
        ' Don't import the workbook containing macro
        '----------------------------------------
        If FolderPath & FileName <> ThisWorkbook.FullName Then
            
            '----------------------------------------
            ' Open source workbook
            '----------------------------------------
            Set SourceWB = Workbooks.Open( _
                Filename:=FolderPath & FileName, _
                ReadOnly:=True)
            
            '----------------------------------------
            ' Use first worksheet
            '----------------------------------------
            Set SourceWS = SourceWB.Worksheets(1)
            
            '----------------------------------------
            ' Find last row and column
            '----------------------------------------
            SourceLastRow = SourceWS.Cells( _
                SourceWS.Rows.Count, 1).End(xlUp).Row
            
            SourceLastCol = SourceWS.Cells(1, _
                SourceWS.Columns.Count).End(xlToLeft).Column
            
            '----------------------------------------
            ' Import data
            '----------------------------------------
            If FirstFile = True Then
                
                'First file:
                'Copy header + data
                
                DestWS.Cells.Clear
                
                SourceWS.Range( _
                    SourceWS.Cells(1, 1), _
                    SourceWS.Cells(SourceLastRow, SourceLastCol) _
                ).Copy Destination:=DestWS.Cells(1, 1)
                
                FirstFile = False
                
            Else
                
                'Other files:
                'Copy data WITHOUT header
                
                LastRow = DestWS.Cells( _
                    DestWS.Rows.Count, 1).End(xlUp).Row
                
                StartRow = LastRow + 1
                
                If SourceLastRow > 1 Then
                    
                    SourceWS.Range( _
                        SourceWS.Cells(2, 1), _
                        SourceWS.Cells(SourceLastRow, SourceLastCol) _
                    ).Copy Destination:=DestWS.Cells(StartRow, 1)
                    
                End If
                
            End If
            
            '----------------------------------------
            ' Close source workbook
            '----------------------------------------
            SourceWB.Close SaveChanges:=False
            
        End If
        
        '----------------------------------------
        ' Next file
        '----------------------------------------
        FileName = Dir()
        
    Loop
    
    '----------------------------------------
    ' Format combined data
    '----------------------------------------
    DestWS.Columns.AutoFit
    
    MsgBox "All Excel files have been successfully combined!" & vbCrLf & _
           "Folder: " & FolderPath, _
           vbInformation, "Import Completed"

ExitHandler:

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    
    Exit Sub

ErrorHandler:

    MsgBox "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "VBA Error"
    
    On Error Resume Next
    
    If Not SourceWB Is Nothing Then
        SourceWB.Close SaveChanges:=False
    End If
    
    Resume ExitHandler

End Sub