How to merge multiple Excel files into one

Fri Sep 20, 2019 by Adel Omar Mohammed in "Excel", "How-To", "VBA" "Excel", "How-To", "VBA"

How to merge multiple Excel files into one

How to merge Excel files with VBA

The tutorial shows how to combine Excel files into one: by running VBA.

It is a lot easier to process data in a single file instead of switching between numerous sources. However, merging multiple Excel workbooks into one file could be a cumbersome and long process, especially if the workbooks you need to combine contain multiple worksheets. So, how would you approach the problem? Will you be coping sheets manually or with VBA code? Or, do you use one of the specialized tools to merge Excel files? Below you will find a good ways to handle this task.

If you have multiple Excel files that have to merged into one file, a faster way would be to automate the process with a VBA macro.

Below you will find the VBA code that copies all sheets from all Excel files that you select into one workbook.

Important note! The macro works with the following caveat – the files to be merged should not be open physically or in memory. In such a case, you will get a run-time error.

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 "Procesed " & 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

How to add this macro to your workbook If you’d like to insert the macro in your own workbook, perform these usual steps:

Press Alt + F11 to open the Visual Basic Editor.
Right-click ThisWorkbook on the left pane and select Insert > Module from the context menu.
In the window that appears (Code Editor window), paste the above code.

How to use the MergeExcelFiles macro

Open the Excel file where you want to merge sheets from other workbooks and do the following:

Press Alt + F8 to open the Macro dialog.
Under Macro name, select MergeExcelFiles and click Run.
run-macro-merge-files

The standard explorer window will open, you select one or more workbooks you want to combine, and click Open. To select multiple files, hold down the Ctrl key while clicking the file names.

Depending on how many files you’ve selected, allow the macro a few seconds or minutes to process them. After the macro completes, it will notify you how many files have been processed and how many sheets have been merged.

hope this can help you , Let me know if you have any questions or comments.