Still not clear on the header thing and adding them up. Let's start with getting all the sheets into 1 workbook. Place this code in sheet code and call/run testthat. Dave
Option Explicit
Private Sub testthat()
Dim FileNm As Object, Cnt As Integer
Dim TargetFiles As FileDialog, sht As Worksheet, Cnt2 As Integer
'prompt user to select files
 Set TargetFiles = Application.FileDialog(msoFileDialogOpen)
 With TargetFiles
 .AllowMultiSelect = True
 .Title = "Multi-select target data files:"
 .ButtonName = ""
 .Filters.Clear
 .Filters.Add ".xlsx files", "*.xlsx"
 .Show
 End With
If TargetFiles.SelectedItems.Count = 0 Then
 MsgBox "PICK A FILE!"
 Exit Sub
 End If
On Error GoTo Below
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Cnt2 = ThisWorkbook.Sheets.Count
For Cnt = 1 To TargetFiles.SelectedItems.Count
 'open the file and assign the workbook/worksheet
Set FileNm = Workbooks.Open(TargetFiles.SelectedItems(Cnt))
For Each sht In Workbooks(FileNm.Name).Worksheets
sht.Copy After:=ThisWorkbook.Sheets(Cnt2)
Cnt2 = Cnt2 + 1
Next sht
Workbooks(FileNm.Name).Close SaveChanges:=False
Next Cnt
Below:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "File Error"
End Sub