Merge Excel files - How to merge multiple Excel files

Having your data spread across multiple Excel files? Want to merge Excel files into a single Excel Workbook? Today we going to explore just that – how to merge multiple Excel Workbook spreadsheets into a single Workbook. The obvious tool for this task is Visual Basic for Applications so let us jump right in.

Want to merge Worksheets or CSV files instead?:

Merge Excel files manually

Open the destination Workbook

Open the destination Excel Workbook (in our example Destination.xlsx) to which you want to copy the Worksheets of the remaining Workbooks (in our example Source.xlsx).

The further steps need to be repeated for each Excel Workbook you want to copy to the destination Workbook.

Open (each) the Workbook you want to merge and copy Worksheets

Open each Excel Workbook you want to merge with the destination Workbook.

Next right-click on each Worksheet you want to copy, click Move or Copy.... In the Move or Copy Window select the destination Workbook (Destination.xlsx in our case). Next:

If you want to move (cut & paste) the Worksheet – simply click the OK button to proceed
If you want to copy (copy & paste) the Worksheet – select the Create a Copy checkbox and click the OK button

Repeat Step 2 for each Workbook you want to copy to the destination Workbook.

Merge Excel files using VBA

Let us assume we have a couple of files listed in our directory (in my example these are File1 and File2.xlsx). What we want to do is create a new Excel Workbook and copy Worksheets to this new Workbook. I wanted however to account for 2 typical scenarios:

Copy ALL worksheets
Copy only a single worksheet – with a specific name

The code below supports both these scenarios.

Merge Excel files code

Use the MergeExcelFiles Sub procedure below to merge any number of Workbooks:

Sub MergeExcelFiles(fileNames() As String, Optional worksheetName As String = vbNullString, Optional mergedFileName As String = "merged.xlsx")
    Dim fileName As Variant, wb As Workbook, ws As Worksheet, destWb As Workbook, excelApp As Application
    Set excelApp = New Application
    Set destWb = excelApp.Workbooks.Add
    
    For Each fileName In fileNames
        Set wb = excelApp.Workbooks.Open(fileName, ReadOnly = True)
        For Each ws In wb.Sheets
            If worksheetName <> vbNullString Then
                If ws.Name = worksheetName Then ws.Copy After:=destWb.Sheets(destWb.Sheets.Count)
            Else
                ws.Copy After:=destWb.Sheets(destWb.Sheets.Count)
            End If
        Next ws
        wb.Close SaveChanges:=False
    Next fileName
    
    destWb.SaveAs ThisWorkbook.Path & "\" & mergedFileName
    destWb.Close SaveChanges:=False
    excelApp.Quit
    Set destWb = Nothing: Set excelApp = Nothing
    MsgBox "Merge completed!"
End Sub

How to use the procedure above? Below I create a simple Test procedure that lists the Excel files within the Workbook directory and merges the Workbooks.

Sub TestMerge()
    Dim fileNames(0 To 1) As String
    fileNames(0) = ThisWorkbook.Path & "\File1.xlsx"
    fileNames(1) = ThisWorkbook.Path & "\File2.xlsx"
    '...

    'Merge all worksheets in listed files
    MergeExcelFiles fileNames
    
    'Merge only worksheets named "SomeWs" in listed files and save the merged file as "test.xlsx"
    MergeExcelFiles fileNames, "SomeWs", "test.xlsx"

End Sub

Pretty simple right?

Merge Excel files within a directory

The scenario above works pretty well for situations where we want to list explicitly files we want to merge into a single Excel Workbook. How about when we have tons of files? Or to make it more simple, if we want to merge all files within a SINGLE directory. As this is also a typical scenario I have modified the above Test procedure to accommodate just that:

Sub TestMergeDirectory()
    Dim fileNames() As String, currIndex As Long, fileName As String, directory As String
    
    directory = ThisWorkbook.Path & "\SomeDir\"
    ReDim fileNames(0 To 0) As String
    fileName = Dir(directory)
    fileNames(0) = directory & fileName
    Do Until fileName = vbNullString
        currIndex = currIndex + 1
        ReDim Preserve fileNames(0 To currIndex) As String
        fileName = Dir
        fileNames(currIndex) = directory & fileName
    Loop
    ReDim Preserve fileNames(0 To currIndex - 1) As String
    
    MergeExcelFiles fileNames
End Sub

What happens is that we use the VBA Dir function to loop through all files within a selected directory. As we go along we modify our VBA Array redefining its size and adding additional items.

Download the code

Want to download the code above and the example?
Download

Merge Excel files in a whole directory structure

One last scenario that came to mind is when we have a whole directory structure (directories within other directories) containing files with want to merge. As the VBA Dir function works only for a single directory and does not traverse any directories within we would need to amend additionally the code above. If needed I recommend reading my post on How to traverse directories using the VBA Dir function.

Related posts: