Multiple Copy and paste from closed workbooks in sequence

[SWETHAKHATRI88]

I have in total 5 excel workbooks. Workbook 5 has the VBA code. Workbooks 1-4 have the same sheet names but with different content. I do have the below code to import 2 sheets from workbook 1 into workbook 5. Is there any way I can look for the the next blank cell in workbook 5 and import the corresponding data from workbooks 2, 3 and 4.

Final Output should be
Workbook 1 - Copy Sheets 1 and 2 into Workbook 5 (Master Workbook)
Workbook 2 - Copy data from Sheets 1 and 2 from Row 6 only into Workbook 5 next available blank row
Workbook 3 - Copy data from Sheets 1 and 2 from Row 6 only into Workbook 5 next available blank row
Workbook 4 - Copy data from Sheets 1 and 2 from Row 6 only into Workbook 5 next available blank row

My code for workbook 1 is below

Application.ScreenUpdating = False
Set ClosedBook = Workbooks.Open("C:\Status Reports\Workbook1.xlsb")
Closedbook.Sheets("Sheet1").CopyAfter: = ThisWorkbook.Sheets("Control")
Closedbook.Sheets("Sheet2").CopyAfter: = ThisWorkbook.Sheets("Control")
Closedbook.Close SaveChanges: = False

Thanks

 

[mumps]

Are workbooks 1 to 5 the only workbooks saved in "C:\Status Reports\"? Do they all have an "xlsb" extension?

 

[SWETHAKHATRI88]

Are workbooks 1 to 5 the only workbooks saved in "C:\Status Reports\"? Do they all have an "xlsb" extension?

Yes pls. All are stored in the same folder and have xlsb extension

 

[mumps]

What is the actual name of Workbook1?

 

[SWETHAKHATRI88]

What is the actual name of Workbook1?

CLDUMP.xlsb

 

[mumps]

(UNTESTED) This macro assumes that the folder contains only the 5 workbooks.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWB As Workbook, desWS As Worksheet, srcWB As Workbook, LastRow As Long, lCol As Long, ws As Worksheet
    Set desWB = ThisWorkbook
    Set desWS = desWB.Sheets("Control")
    Const strPath As String = "C:\Status Reports\"
    Set srcWB = Workbooks.Open("C:\Status Reports\CLDUMP.xlsb")
    Sheets(Array("Sheet1", "Sheet2")).Copy After:=desWB.Sheets(desWB.Sheets.Count)
    srcWB.Close False
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        If srcWB.Name <> desWB.Name And srcWB.Name <> "CLDUMP.xlsb" Then
            For Each ws In Sheets(Array("Sheet1", "Sheet2"))
                With ws
                    LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    lCol = .Cells(6, .Columns.Count).End(xlToLeft).Column
                    .Range("A" & LastRow).Resize(, lCol).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
                End With
            Next ws
            srcWB.Close False
        End If
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub