copying sheets from multiple workbooks to existing workbook- VBA

Question

I am trying to copy 4 sheets from 4 different workbooks into the 'Master' workbook that I am in

The below code just does it for one sheet but I want to do it for all 4 sheets

Also, currently the code below opens up the source sheet but I don't want to open source sheets. If I remove '.Open" from below file path then it says subscript out of range

Thanks

Sub Copysheets()

Dim source As Workbook
Dim Master As Workbook

Set source = Workbooks.Open("\\filepath\filename.xlsx")
Set Master = Workbooks.Open("\\filepath\filename.xlsm")

Dim sourcesheet As Worksheet

For Each sourcesheet In source.Sheets

sourcesheet.Copy After:=Master.Sheets(Master.Sheets.Count)
Next


End Sub

Answer 1

If you have a task that you need to do repeated times, usually it's a good idea outsource the task to a subroutine.

The following routine gets 2 parameters, the first is the master (the workbook where you want to copy the sheets into) and a filename (with the name of the file to be opened and copied). This copy-routine doesn't care about your business logic, it simply copies the sheets:

Sub Copysheets(masterWB As Workbook, sourceWBName As String)
    Dim sourceWB As Workbook, sourceSheet As Worksheet
    Set sourceWB = Workbooks.Open(sourceWBName)

    For Each sourceSheet In sourceWB.Sheets
        sourceSheet.Copy After:=masterWB.Sheets(masterWB.Sheets.Count)
    Next
    ' Don't forget to close the file afterwards:
    sourceWB.Close SaveChanges:=False
End Sub

You could then call the the routine like this (this piece of code handles your business logic but doesn't care about how the copy is done):

Sub CopySheetsFrom4Workbooks()
    Dim masterWB As Workbook
    Set masterWB = Workbooks.Open("\\filepath\filename.xlsm")
    
    Copysheets masterWB, "\\filepath\filename1.xlst"
    Copysheets masterWB, "\\filepath\filename2.xlst"
    Copysheets masterWB, "\\filepath\filename3.xlst"
    Copysheets masterWB, "\\filepath\filename4.xlst"

    masterWB.Save
End Sub

or, for example, use a loop to copy sheets of all files:

Sub CopyAllMyWorkbooks()
    Dim masterWB As Workbook
    Set masterWB = Workbooks.Open("\\filepath\filename.xlsm")
    
    Dim sourceFilename As String
    sourceFilename = Dir("\\filepath\filename*.xlst")
    Do While sourceFilename <> ""
        Copysheets masterWB, sourceFilename
        sourceFilename = Dir
    Loop
End Sub