We have 3 Excel files – File1, File2, and File3 – which we are going to merge into one sheet or in one workbook.
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.
Example 1 – Merge Multiple Files 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.
- Copy the following code and paste it in the module.
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 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 image below).
Example 2 – Combine Multiple Files into One Sheet in an Active Workbook in Excel
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Add the code below in the module.
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
Run the code and you will get all the data from the multiple opened Excel files in one sheet in an active Excel workbook.
Example 3 – Merge Multiple Files into a New Workbook as Individual Sheets in Excel
Steps:
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Add the following code in the Module.
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
Run the code and you will get all the opened Excel files in a new Excel workbook as individual sheets.
<< Go Back To Merge Excel File | Merge in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!