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.
Other Interesting Articles
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 ”.
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
Source: ablebits.com
- 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.
