Excel - Consolidating several workbooks with several worksheets

Occasional Visitor

Need assistance with summing up 35 workbooks and its 15 separate worksheets into 1 workbook with each worksheet separate and summed as well.

1 Reply


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?



'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.



I know I don't know anything (Socrates)