How to Merge Multiple Excel Files into One Sheet by VBA (3 Examples)

Implementing VBA macro is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you how to merge multiple Excel files into one sheet or one workbook if needed in Excel with the VBA macro.

How to Merge Multiple Excel Files Into One Sheet by VBA: 3 Examples

Look at the following pictures. We have 3 Excel files – File1, File2, and File3 – and we are going to merge these three files into one sheet or in one workbook if needed.

Remember: The files you need to copy the information form (e.g. File1, File2, File3) need to be open while running the macro. Because Excel will loop through the open files and copy the information into a new sheet or workbook.

1. Merge Multiple Files into One Sheet in a New Workbook in Excel

This criterion will let you know how to merge File1, File2, and File3 into one sheet in a new workbook in Excel.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• In the code window, copy the following code and paste it.

Sub MergeMultipleSheetsToNew()
On Error GoTo eh
'declare variables to hold the objects required
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsDestination As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
   Dim iRws As Integer
   Dim iCols As Integer
   Dim totRws As Integer
   Dim strEndRng As String
   Dim rngSource As Range
'turn off the screen updating to speed things up
   Application.ScreenUpdating = False
'first create new destination workbook
   Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
   strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
         For Each sh In wbSource.Worksheets
'get the number of rows and columns in the sheet
            sh.Activate
            ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
            iRws = ActiveCell.Row
            iCols = ActiveCell.Column
'set the range of the last cell in the sheet
            strEndRng = sh.Cells(iRws, iCols).Address
'set the source range to copy
            Set rngSource = sh.Range("A1:" & strEndRng)
'find the last row in the destination sheet
           wbDestination.Activate
           Set wsDestination = ActiveSheet
           wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
           totRws = ActiveCell.Row
'check if there are enough rows to paste the data
           If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
               MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
               GoTo eh
           End If
'add a row to paste on the next row down
           If totRws <> 1 Then totRws = totRws + 1
           rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
      Next sh
   End If
   Next wb
'now close all the open files except the one you want
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb
'clean up the objects to release the memory
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsDestination = Nothing
   Set rngSource = Nothing
   Set wb = Nothing
'turn on the screen updating when complete
   Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

Your code is now ready to run.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will get all the data from the multiple opened Excel files in one sheet of a new Excel workbook (see the picture below to understand more).

2. Combine Multiple Files into One Sheet in an Active Workbook in Excel

This criterion will let you know how to combine File1, File2, and File3 into one sheet in an active workbook in Excel.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub MergeMultipleSheetsToActive()
   On Error GoTo eh
'declare variables to hold the objects required
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsDestination As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
   Dim iRws As Integer
   Dim iCols As Integer
   Dim totRws As Integer
   Dim rngEnd As String
   Dim rngSource As Range
'set the active workbook object for the destination book
   Set wbDestination = ActiveWorkbook
'get the name of the active file
   strDestName = wbDestination.Name
'turn off the screen updating to speed things up
   Application.ScreenUpdating = False
'first create new destination worksheet in your Active workbook
   Application.DisplayAlerts = False
'resume next error in case sheet doesn't exist
   On Error Resume Next
   ActiveWorkbook.Sheets("Consolidation").Delete
'reset error trap to go to the error trap at the end
   On Error GoTo eh
   Application.DisplayAlerts = True
'add a new sheet to the workbook
   With ActiveWorkbook
      Set wsDestination = .Sheets.Add(After:=.Sheets(.Sheets.Count))
      wsDestination.Name = "Consolidation"
   End With
'now loop through each of the workbooks open to get the data
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
            For Each sh In wbSource.Worksheets
'get the number of rows in the sheet
               sh.Activate
               ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
               iRws = ActiveCell.Row
               iCols = ActiveCell.Column
               rngEnd = sh.Cells(iRws, iCols).Address
               Set rngSource = sh.Range("A1:" & rngEnd)
'find the last row in the destination sheet
               wbDestination.Activate
               Set wsDestination = ActiveSheet
               wsDestination.Cells.SpecialCells(xlCellTypeLastCell).Select
               totRws = ActiveCell.Row
'check if there are enough rows to paste the data
               If totRws + rngSource.Rows.Count > wsDestination.Rows.Count Then
                  MsgBox "There are not enough rows to place the data in the Consolidation worksheet."
                  GoTo eh
               End If
'add a row to paste on the next row down if you are not in row 1
               If totRws <> 1 Then totRws = totRws + 1
               rngSource.Copy Destination:=wsDestination.Range("A" & totRws)
           Next sh
         End If
   Next wb
'now close all the open files except the one you want
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb
 
'clean up the objects to release the memory
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsDestination = Nothing
   Set rngSource = Nothing
   Set wb = Nothing
'turn on the screen updating when complete
   Application.ScreenUpdating = False
Exit Sub
eh:
MsgBox Err.Description
End Sub

Your code is now ready to run.

Run the code and you will get all the data from the multiple opened Excel files in one sheet in an active Excel workbook (see the picture above to understand more).

3. Merge Multiple Files into a New Workbook as Individual Sheets in Excel

This criterion will let you know how to combine File1, File2, and File3 into a new workbook as individual sheets in Excel.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub MergeMultipleFiles()
On Error GoTo eh
'declare variables to hold the objects required
   Dim wbDestination As Workbook
   Dim wbSource As Workbook
   Dim wsSource As Worksheet
   Dim wb As Workbook
   Dim sh As Worksheet
   Dim strSheetName As String
   Dim strDestName As String
'turn off the screen updating to speed things up
   Application.ScreenUpdating = False
'first create new destination workbook
   Set wbDestination = Workbooks.Add
'get the name of the new workbook so you exclude it from the loop below
   strDestName = wbDestination.Name
'now loop through each of the workbooks open to get the data but exclude your new book or the Personal macro workbook
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         Set wbSource = wb
         For Each sh In wbSource.Worksheets
            sh.Copy After:=Workbooks(strDestName).Sheets(1)
         Next sh
      End If
   Next wb
'now close all the open files except the new file and the Personal macro workbook.
   For Each wb In Application.Workbooks
      If wb.Name <> strDestName And wb.Name <> "PERSONAL.XLSB" Then
         wb.Close False
      End If
   Next wb
'remove sheet one from the destination workbook
   Application.DisplayAlerts = False
   Sheets("Sheet1").Delete
   Application.DisplayAlerts = True
'clean up the objects to release the memory
   Set wbDestination = Nothing
   Set wbSource = Nothing
   Set wsSource = Nothing
   Set wb = Nothing
'turn on the screen updating when complete
   Application.ScreenUpdating = False
Exit Sub
eh:
   MsgBox Err.Description
End Sub

Your code is now ready to run.

Run the code and you will get all the opened Excel files in a new Excel workbook as individual sheets (see the picture above to understand more).

Download Practice Template

You can download the VBA code from this free practice Excel template.

Conclusion

This article showed you how to merge multiple Excel files into one sheet and one workbook in Excel by implementing the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

<< Go Back To Merge Excel File | Merge in Excel | Learn Excel