Sanjida Ahmed
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.
Download Practice Template
You can download the VBA code from this free practice Excel template.
3 Criteria to Merge Multiple Excel Files Into One Sheet Using VBA
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 SubYour 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 SubYour 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 SubYour 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).
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.
