Copy multiple workbooks to one master sheet

[boele]

Hi guys, I use the following code to try to copy multiple worksheets in one file:

Option Explicit
 
Sub CombineData()
    Dim oWbk As Workbook
    Dim sFil As String
    Dim sPath As String
     
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
    .AskToUpdateLinks = False
 
         '   On Error GoTo exithandler
        sPath = "C:\Users\kber\Desktop\Kevin\Projecten\Integratie advieslijsten"
        ChDir sPath
        sFil = Dir("*.xls")
        Do While sFil <> "" 'will start LOOP until all files in folder sPath have been looped through
             
            Set oWbk = Workbooks.Open(sPath & Application.PathSeparator & sFil) 'opens the file
            oWbk.Sheets.Copy
            oWbk.Close False 'close source workbook
            sFil = Dir
             
        Loop ' End of LOOP
         
exithandler:
        .ScreenUpdating = True
        .DisplayAlerts = True
        .EnableEvents = True
    End With
    
 
    
End Sub

The code opens all four workbooks and now is my question how to copy these workbooks to one (master)workbook. Thank you in advance!

 

[oldbrewer]

you are using the words workbooks and worksheets and I am confused
does second workbook contain, say 4 worksheets and do you wish to copy these into one sheet of a new empty master workbook

 

[boele]

Thank you for your response oldbrewer. What I want to do is to copy all the sheets from the four workbooks in the folder to one file. So for example Workbook 1: 4 sheets Workbook 2: 2 sheets, Workbook 3: 1 sheet and Workbook 4 contains 2 sheets. I want to copy the total of (4+2+1+4) 11 sheets to one masterfile. Thanks!

 

[oldbrewer]

I suggest you create your master workbook, open it, and turn on record macro. Then open workbook 1, go to sheet 1 edit select all, copy, go back to master and paste, then stop macro. Look at the code and proceed from there. you will need to locate the first empty row in the master before each paste.

 

[boele]

I suggest you create your master workbook, open it, and turn on record macro. Then open workbook 1, go to sheet 1 edit select all, copy, go back to master and paste, then stop macro. Look at the code and proceed from there. you will need to locate the first empty row in the master before each paste.

Used some other code and works fine now