Excel - Consolidating several workbooks with several worksheets

tsbabutler 

If there are multiple workbooks need to be merged into one, you can apply VBA codes to quickly achieve it. In the link below you will find some examples with VBA and without.

How to combine multiple workbooks into one master workbook in Excel?

VBA CODE

Sub_merge_files()
 
'Merges all spreadsheets of the selected Excel files into this workbook
 
Dim wbSource As Workbook
Dim sh As Worksheet
Dim Arr files As Variant
Dim cntFile As Long
 
'Disable screen updating
Application.ScreenUpdating = False
 
'Let user select files
arrfiles = Application.GetOpenFilename(filefilter:="Excel files (*.xls*),*.xls*", MultiSelect:=True)
 
'Has at least one file been selected?
If IsArray(arrfiles) Then
     'Loop over all selected files
     For cntFile = 1 To UBound(arrfiles)
     
         'Open current workbook
         Set wbSource = Workbooks.Open(Filename:=arrfiles(cntFile), UpdateLinks:=False, ReadOnly:=True)
         
         'Loop through all spreadsheets
         For Each sh In wbSource.Worksheets
             'Copy current spreadsheet
             sh.Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
         Next sh
         
         'Close current workbook
         wbSource.Close savechanges:=False
         
     Next cntFile
End If
'Enable screen updating
Application.ScreenUpdating = True
end sub

Hope I was able to help you with this information.

NikolinoDE

I know I don't know anything (Socrates)