ExcelInsider Team
Manually copying and pasting data from Excel workbooks into a single master sheet is a repetitive and time-consuming process. Fortunately, by using the power of VBA, you can create macros that automate this task with just a few clicks. This not only saves you a significant amount of time but also reduces the risk of human error. In this article, we will guide you through three different VBA macros to merge data from multiple Excel files into a new workbook, separate sheets, or an existing workbook.
To merge multiple Excel files into one sheet using VBA:
➤ Place all your source files in a single folder.
➤ Open the Visual Basic Editor and insert a new module.
➤ Copy and paste the VBA code.
➤ Run the macro to automatically merge the data into a single sheet.
Sub IntegrateFilesToOneSheet()
Dim wbDest As Workbook, wbSrc As Workbook
Dim wsDest As Worksheet
Dim FolderPath As String, FileName As String
Dim LastRow As Long, PasteRow As Long
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
If fDialog.Show <> -1 Then Exit Sub
FolderPath = fDialog.SelectedItems(1) & "\"
Set wbDest = Workbooks.Add
Set wsDest = wbDest.Sheets(1)
PasteRow = 1
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""
Set wbSrc = Workbooks.Open(FolderPath & FileName)
LastRow = wbSrc.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
wbSrc.Sheets(1).Range("A1:D" & LastRow).Copy wsDest.Cells(PasteRow, 1)
PasteRow = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 2
wbSrc.Close False
FileName = Dir
Loop
End Sub
Integrating Data from Multiple Excel Files into One Sheet in a New Workbook
This method works well when you want to combine data from multiple source files into a single, brand-new workbook. The macro will automatically create a new file and stack all the data from your source files on top of each other.
Suppose we have a folder named “Data” containing three workbooks: “Sales of North”, “Sales of South”, and “Sales of West”.
Sales of North: This sample workbook contains sales data for various kitchen appliances, covering total sales as well.
Sales of South: This workbook contains sales data for fitness-related products and total sales in the South region.
Sales of West: This workbook contains sales data for travel and camping gear.
First, you need to open the VBA editor.
➤ Go to the Developer tab and click on Visual Basic.
➤ In the VBA editor, go to Insert > Module.
➤ Now, copy and paste the following code into the new module window and hit Run.
Sub IntegrateFilesToOneSheet()
Dim wbDest As Workbook, wbSrc As Workbook
Dim wsDest As Worksheet
Dim FolderPath As String, FileName As String
Dim LastRow As Long, PasteRow As Long
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
If fDialog.Show <> -1 Then Exit Sub
FolderPath = fDialog.SelectedItems(1) & "\"
Set wbDest = Workbooks.Add
Set wsDest = wbDest.Sheets(1)
PasteRow = 1
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""
Set wbSrc = Workbooks.Open(FolderPath & FileName)
LastRow = wbSrc.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
wbSrc.Sheets(1).Range("A1:D" & LastRow).Copy wsDest.Cells(PasteRow, 1)
PasteRow = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 2
wbSrc.Close False
FileName = Dir
Loop
End Sub➧ Set wbDest = Workbooks.Add: This creates a brand-new Excel workbook to be used as your master file.
➧ Do While fileName <> "": This loop goes through every Excel file (.xls or .xlsx) in the selected folder.
➧ wsSrc.Sheets(1).Range("A1:D" & LastRow).Copy wsDest.Cells(PasteRow, 1): This line copies data from each source file and pastes it into the new workbook, starting at a specific row.
➧ PasteRow = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 2: This finds the next empty row in the new sheet, leaving one blank row for separation, so the data from the next file is pasted correctly below the previous data.
➧ wbSrc.Close False: After copying the data, the macro closes each source workbook without saving any changes.
The macro will then prompt you to select the folder containing your workbooks.
➤ Select the folder and click OK.
As a result, a new workbook will be created, and all the data from your source files will be merged into a single new workbook.
Importing Multiple Excel Files as Separate Sheets into a New Workbook
Sometimes, you might need to keep the data from each file separate but within a single workbook. This macro will create a new workbook and import each source file into its dedicated sheet.
➤ Follow the previous steps, open the VBA editor, and go to Insert > Module.
➤ Copy and paste the following code into the new module and click Run.
Sub ImportFilesAsSheets()
Dim wbDest As Workbook, wbSrc As Workbook
Dim FolderPath As String, FileName As String
Dim wsNew As Worksheet
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
If fDialog.Show <> -1 Then Exit Sub
FolderPath = fDialog.SelectedItems(1) & "\"
Set wbDest = Workbooks.Add
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""
Set wbSrc = Workbooks.Open(FolderPath & FileName)
wbSrc.Sheets(1).Copy After:=wbDest.Sheets(wbDest.Sheets.Count)
wbDest.Sheets(wbDest.Sheets.Count).Name = Left(FileName, InStrRev(FileName, ".") - 1)
wbSrc.Close False
FileName = Dir
Loop
Application.DisplayAlerts = False
For Each wsNew In wbDest.Sheets
If wsNew.Name Like "Sheet*" Then wsNew.Delete
Next
Application.DisplayAlerts = True
End Sub➧ Set wbDest = Workbooks.Add: This creates a new, blank workbook to be the destination for all the sheets.
➧ Do While fileName <> "": This loop processes each Excel file in the selected folder.
➧ wbSrc.Sheets(1).Copy After:=wbDest.Sheets(wbDest.Sheets.Count): This line copies the first sheet from each source workbook and places it as a new, separate tab at the end of the new master workbook.
➧ wbDest.Sheets(wbDest.Sheets.Count).Name = Left(fileName, InStrRev(fileName, ".") - 1): This command renames each newly copied sheet to match the name of its original file (e.g., it changes "Sales of North.xlsx" to just "Sales of North").
➧ wbSrc.Close False: The macro closes each source workbook without saving any changes.
➤ Now, select your source folder and click OK.
Finally, a new workbook will be created with each of your source files imported as a separate worksheet, making it easy to review the data from each region individually.
Collecting Data from Multiple Excel Files into One Sheet in an Existing Workbook
If you want to collect data from multiple files directly into your current, active workbook, you can use the macro below. This macro will automatically find the last empty row and collect the data from each source file.
➤ Open the VBA editor and click Insert > Module.
➤ Paste the following code into the new module and hit Run.
Sub CollectFilesIntoActiveWorkbook()
Dim wbSrc As Workbook
Dim wsDest As Worksheet
Dim FolderPath As String, FileName As String
Dim LastRowSrc As Long, LastRowDest As Long
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
If fDialog.Show <> -1 Then Exit Sub
FolderPath = fDialog.SelectedItems(1) & "\"
Set wsDest = ThisWorkbook.Sheets("Sheet1")
LastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1
FileName = Dir(FolderPath & "*.xls*")
Do While FileName <> ""
Set wbSrc = Workbooks.Open(FolderPath & FileName)
LastRowSrc = wbSrc.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
wbSrc.Sheets(1).Range("A1:D" & LastRowSrc).Copy wsDest.Cells(LastRowDest, 1)
LastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 2
wbSrc.Close False
FileName = Dir
Loop
End Sub➧ Set wsDest = ThisWorkbook.Sheets("Sheet1"): This sets the destination for the data to a specific sheet (in this case, "Sheet1") in the workbook where the macro is running.
➧ LastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 1: This finds the very last row with data in the destination sheet and sets the paste location to the first empty row below it.
➧ Do While fileName <> "": This loop goes through every Excel file in the selected folder.
➧ wbSrc.Sheets(1).Range("A1:D" & LastRowSrc).Copy wsDest.Cells(LastRowDest, 1): This copies the data from each source file and pastes it into the destination sheet, starting at the last empty row.
➧ LastRowDest = wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Row + 2: This updates the paste location to the next empty row, plus a blank row for spacing, ready for the data from the next file.
➧ wbSrc.Close False: After copying, the macro closes each source workbook without saving.
➤ Choose the folder and click OK.
The data from all files will be merged into your active worksheet, creating a single, consolidated list.
Frequently Asked Questions
Can I merge only specific sheets from each file?
Yes, in the VBA code, replace Sheets(1) with the exact sheet name you want to copy (e.g., Sheets(“SalesData”)).
Will this macro overwrite my existing data in the master sheet?
If you run the code multiple times without clearing the sheet, it will append data below existing rows. To avoid overwriting data, clear the sheet first before running the macro.
How do I handle large numbers of files without slowing Excel down?
Simply, turn off Application.ScreenUpdating and Application.Calculation at the start of the macro and turn them back on at the end. This can significantly improve performance.
Concluding Words
Above, we have explored several ways to merge multiple Excel files into one sheet using VBA. Whether you need to combine data into a new single workbook, separate sheets, or an existing workbook, these codes can help you solve your tasks. If you have any further questions or need assistance with other VBA tasks, feel free to share them below.
