Combination of multiple excel workbooks with multiple sheets (not in a single sheet)

LaSta95 

Try this.

The target workbook should already have the 5 correctly named sheets.

It should be the active workbook when you run the macro.

You'll be prompted for each workbook to process.

Click Cancel when you want to stop.

Sub CombineWorkbooks()
    Dim wbkS As Workbook
    Dim wbkT As Workbook
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim strFile As String
    Dim lngS As Long
    Dim lngT As Long
    Application.ScreenUpdating = False
    Set wbkT = ActiveWorkbook
    Do
        strFile = Application.GetOpenFilename( _
            FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
            Title:="Select a workbook to process. Click Cancel to stop")
        If strFile = "False" Then Exit Do
        Set wbkS = Workbooks.Open(Filename:=strFile)
        For Each wshS In wbkS.Worksheets
            Set wshT = wbkT.Worksheets(wshS.Name)
            lngS = 0
            lngT = 0
            On Error Resume Next
            lngS = wshS.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngT = wshT.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            On Error GoTo 0
            If lngS > 1 Then
                If lngT = 0 Then
                    wshS.Range("A1:A" & lngS).EntireRow.Copy Destination:=wshT.Range("A1")
                Else
                    wshS.Range("A2:A" & lngS).EntireRow.Copy Destination:=wshT.Range("A" & lngT + 1)
                End If
            End If
        Next wshS
        wbkS.Close SaveChanges:=False
    Loop
    Application.ScreenUpdating = True
End Sub