Option Explicit

Sub Worksheets_All_2_1_test()

    Worksheets_All_2_1 _
            Workbooks("Листы.xlsx"), ThisWorkbook.Sheets(1)

End Sub

Function Worksheets_All_2_1( _
        wb_Sour As Workbook, _
        ws_Dest As Worksheet)
' Скопировать содержание всех листов книги wb_Sour на ws_Dest
Dim ws As Worksheet

    For Each ws In wb_Sour.Worksheets

        Range_2_Cell Range_Sour(ws), Cell_Dest(ws_Dest)

    Next
End Function

Function Range_2_Cell( _
        r As Range, _
        cell As Range)
    
    r.Copy cell

End Function

Function Range_Sour( _
        ws As Worksheet) _
        As Range

    Set Range_Sour = Range_Headers_No(ws.UsedRange)

End Function

Function Range_Headers_No( _
        r As Range) _
        As Range
    
    Set Range_Headers_No = _
            Range_Resize(r, -1, 0).Offset(1, 0)

End Function

Function Range_Resize( _
        r As Range, _
        lRow As Long, _
        lCol As Long) _
        As Range
    
    With r
        Set Range_Resize = .Resize( _
                .Rows.Count + lRow, _
                .Columns.Count + lCol)
    
    End With
End Function

Function Cell_Dest( _
        ws As Worksheet) _
        As Range

    Set Cell_Dest = ws.Cells(Строка_Свободная(ws), 1)

End Function

Function Строка_Свободная( _
        ws As Worksheet) _
        As Long
    ' procedure Checked by test

    Dim r As Range

    Set r = ws.Cells.Find(what:="*", SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious)

    If r Is Nothing Then

        Строка_Свободная = 1

    Else

        Строка_Свободная = r.Row + 1

    End If
End Function