How to merge Excel files with VBA

How to merge Excel files with VBA

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. This MergeExcelFiles macro is written by Alex, one of our best Excel gurus.

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

How to add this macro to your workbook

If you'd like to insert the macro in your own workbook, perform these usual steps:

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

For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Alternatively, you can download the workbook with MergeExcelFiles macro, open it alongside your target workbook (enable macro if prompted), then switch to your own workbook and press Alt + F8 to run the macro. If you are new to using macros in Excel, please follow the detailed steps below.

How to use the MergeExcelFiles macro

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

1. Press Alt + F8 to open the Macro dialog.
2. Under Macro name, select MergeExcelFiles and click Run.
3. 
    
4. 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: