Sub loopthroughdirectory()
    Dim myFile As String
    Dim myPath As String
    Dim myExtension As String
    Dim myMainFile As String
    Dim mainFileFinalRow As Integer
    Dim fileToCopyFinalRow As Integer

    ' Insert the path to the folder in which you have all the excel files
    myPath = "C:\Users\Hendro\Desktop\test\folder_to_loop\"
    myExtension = "*.xls*"
    myFile = Dir(myPath & myExtension)
    MsgBox (myFile)

    Do While Len(myFile) > 0

        Workbooks.Open (myPath & myFile)
        Workbooks(myFile).Worksheets(1).Activate

        fileToCopyFinalRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        MsgBox (fileToCopyFinalRow)
        ActiveSheet.Range(Cells(2, 1), Cells(fileToCopyFinalRow, 11)).Copy
        Workbooks(myFile).Close

        mainFileFinalRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ActiveSheet.Paste Destination:=Worksheets(1).Range(Cells(mainFileFinalRow, 1), Cells(mainFileFinalRow + fileToCopyFinalRow - 1, 11))

        myFile = Dir
    Loop
End Sub