Copy data from multiple workbooks to one master workbook

Hi all,

Probably the 6324th time this question has been asked BUT:

I have four excel workbooks in a folder. 2 are .xls and other 2 are .xlsm. None of these workbooks have an equal
amount of sheets. My question is if anyone can help me start up in VBA to open these files, copy the sheets to one
masterfile and close the four workbooks.

Any help for a start-up would be appreciated!

Thank you.

6324 ... well, you probably underestimated :-) (by the way - have you used search?)

Try (it is based on a code I am using elsewhere, so some of variables have strange names :-P) in your masterfile:

Sub copy_all()
Dim my_wbk As Workbook, src_wbk As Workbook, src_wks As Worksheet
Dim ob As Object, pliki As Object, plik As Object
Dim folder As Object, mypath As String
Set my_wbk = ThisWorkbook
mypath = "C:\Users\here_your_real_path\"
Set ob = CreateObject("Scripting.FilesystemObject")
Set folder = ob.GetFolder(mypath)
Set pliki = ob.GetFolder(mypath).Files

With Application
 .ScreenUpdating = False
 .EnableEvents = False
End With

For Each plik In pliki
  Debug.Print plik.Name
  
  If Right(plik.Name, 3) = "xls" Or Right(plik.Name, 4) = "xlsm" Then
     Set src_wbk = Workbooks.Open(folder & "\" & plik.Name)
     For Each src_wks In src_wbk.Worksheets
Debug.Print "   -> " & src_wks.Name
       src_wks.Copy After:=my_wbk.Sheets(my_wbk.Sheets.Count)
     Next src_wks
     src_wbk.Close False
   End If
Next plik

With Application
  .ScreenUpdating = True
  .EnableEvents = True
End With

Set my_wbk = Nothing
Set ob = Nothing
Set folder = Nothing
Set pliki = Nothing
End Sub

but note that there will be problems if sheets in different source files have the same names - of course you can rename them first - just be aware of it.

Hi Kaper,

Thanks for your effort!

Code seems to work fine but excel stucks and does not respond anymore.

Think I know what bugs here. The .xls files contain macros. When I open a .xls file it asks me to "Enable Content". Do you have a clue how to avoid this?

Thanks