How to merge multiple Excel files into one file

25 February 2022

Home » Blogs » Software » How to merge multiple Excel files into one file

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.

TOP TUTORIALS:  How to change the format of date, time, number, and currency in Windows 11

If you are going to merge many files, the fastest way is to use VBA Macros.

Merge multiple Excel files into one

  1. Create a new file.
  2. Press ( Alt + F11 ) to open the Visual Basic Editor.
  3. After the Visual Basic Editor window opens, then click “ ThisWorkbook > Insert > Module ”.
  1. Then copy the following
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)
                wbkSrcBook.Close SaveChanges:=False
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
            MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub


  1. Then click the “ Run ” button to merge.
TOP TUTORIALS:  How to Install Nmap with GUI in Windows 11
  1. Next, select the files you want to merge. Then click “Open“.
  1. Wait for the process to finish.