How to merge 2 workbooks in Excel

Merging 2 different workbook together to form a single workbook is important in using a lot of functions. For example you want to use Vlookup function in solving a particular excel problem and the sheets involved are not in the same workbook. It is important for you to first merge the 2 different workbook or files so that all the tables/sheets will be in the same file or in the same workbook.

The steps below will help you to know how to merge 2 workbooks in excel

1. Keep the 2 different workbooks into a folder
2. Open the 2 workbook and create a new empty workbook
3. Press “alt” “F11” to open microsoft  visual basic for application editor while you cursor is inside the empty workbook
4. Paste the visual basic code into the empty IDE environment . The code is shown below

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

Click insert

On the insert drop down, click module

 An empty page is displayed where you paste the already copied VBA code

After the code has been pasted, click Run

 Select the excel spreadsheet to merge. Then the workbooks will be merged into single workbook