'Source: www.wisdombydata.com

Sub CombineData()
   Dim FolderPath As String
   Dim textFile As Workbook
   Dim S As Worksheet
   Dim OpenFiles() As Variant
   Dim i As Integer
   
    'Prompt User for Folder containing the files
    OpenFiles = Application.GetOpenFilename(Title:="Select Workbooks", MultiSelect:=True)
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
      'Get the Directory
      'Application.FileDialog(msoFileDialogFolderPicker).Show
      'FolderPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
      '*/

For i = 1 To Application.CountA(OpenFiles)
            Set textFile = Workbooks.Open(OpenFiles(i))
            
For Each S In textFile.Worksheets
If S.Name <> "Main" And S.Range("A5").Value <> "" Then
S.Select
LastRow = Range("A10000").End(xlUp).Row
Range("A5", Cells(LastRow, "E")).Copy

ThisWorkbook.Activate

ThisWorkbook.Sheets("Main").Select
Range("A10000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

Else
End If
Next S

'Save and close each document (note: this will prevent unwanted popups for saving and lcosing)
    textFile.Save
    textFile.Close
    Next i
    
'Display message box telling the user that the procces in completed
    MsgBox "Finished"
End Sub