Do you have many Excel files and want to merge them into one?
Maybe you want to merge several daily reports into one monthly report file. Or you want to merge sales reports for each area into one report file.
There are many ways to merge multiple Excel files into one file. If the files that you want to merge are few, you can do it manually. You can do this by “Copy & Paste” the contents of the document, “Move or Copy” sheets, and so on.
If you are going to merge many files, the fastest way is to use VBA Macros.
Merge multiple Excel files into one
- Create a new Excel file.
- Press ( Alt + F11 ) to open the Visual Basic Editor.
- After the Visual Basic Editor window opens, then click “ ThisWorkbook > Insert > Module ”.
- Then copy the following Macro script
Sub MergeExcelFiles() Dim fnameList, fnameCurFile As Variant Dim countFiles, countSheets As Integer Dim wksCurSheet As Worksheet Dim wbkCurBook, wbkSrcBook As Workbook fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True) If (vbBoolean <> VarType(fnameList)) Then If (UBound(fnameList) > 0) Then countFiles = 0 countSheets = 0 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Set wbkCurBook = ActiveWorkbook For Each fnameCurFile In fnameList countFiles = countFiles + 1 Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile) For Each wksCurSheet In wbkSrcBook.Sheets countSheets = countSheets + 1 wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count) Next wbkSrcBook.Close SaveChanges:=False Next Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files" End If Else MsgBox "No files selected", Title:="Merge Excel files" End If End Sub
- Then click the “ Run ” button to merge.
- Next, select the Excel files you want to merge. Then click “Open“.
- Wait for the process to finish.