Combine | Merge Multiple Excel File (7 Ways) All Data into One with VBA Code

Merge Excel Workbooks from a Folder in One File

Below is the VBA code to merge multiple excel files which are entered in a folder in “D” Drive & the Folder name is “Files”. So if your folder name is different then you can change the path according to your drive.

Input Data

Final Output After Merge

VBA CODE

Sub MergeWorkbooks()

Dim FolderPath As String

Dim File As String

Dim i As Long

FolderPath = "D:\Files\"

File = Dir(FolderPath)

Do While File <> ""

     Workbooks.Open FolderPath & File

        ActiveWorkbook.Worksheets(1).Copy _

        after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

        ActiveSheet.Name = Replace(File, ".xlsx", "")

        Workbooks(File).Close

    File = Dir()

Loop

End Sub

2 Combine All Sheets in Workbook into a Single Sheet

This VBA code will help you to consolidate the All Excel Sheets data in One File, but the limitation is that if you excel data in one file but different-different sheets. then this code will consolidate all sheets data from one to a single sheet.

Input Data

Final Output After Merge

VBA CODE

Sub CombineSheets()

    Dim ws As Worksheet

    Dim combinedSheet As Worksheet

    Dim lastRow As Long, combinedLastRow As Long

    Dim sheetName As String

    ' Add a new sheet to store combined data

    Set combinedSheet = ThisWorkbook.Sheets.Add(After:= _

             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))

    combinedSheet.Name = "MergedData"

    ' Loop through each sheet in the workbook

    For Each ws In ThisWorkbook.Worksheets

        ' Skip the combined sheet itself

        If ws.Name <> combinedSheet.Name Then

            ' Get the last row of data in the combined sheet

            combinedLastRow = combinedSheet.Cells(Rows.Count, "A").End(xlUp).Row

            ' Get the sheet name

            sheetName = ws.Name

            ' Loop through each row of data in the current sheet

            For lastRow = 1 To ws.Cells(Rows.Count, "A").End(xlUp).Row

                ' Copy data to the combined sheet

                combinedSheet.Cells(combinedLastRow + lastRow - 1, 1).Value = sheetName

                ws.Rows(lastRow).Copy Destination:=combinedSheet.Rows(combinedLastRow + lastRow - 1)

            Next lastRow

        End If

    Next ws

    MsgBox "All sheets combined successfully!", vbInformation

End Sub

3 Combine Excel Files from a Folder in One File (by Folder Picker)

In this VBA code i have provided a Folder picker, that means when you run this VBA code, then it will show a dialogue box that will ask for the folder path, then you have to select the folder from which you want to combine all the files in one file. after clicking the folder you just click on Ok Button then the process will start and all excel files will be merged into one excel file but in different-different sheets.

Merge with Folder Picker

Final Output After Merge

VBA CODE

Sub CombineWorkbooks()

      Dim myDialog As FileDialog, myFolder As String, myFile As String

      Set myDialog = Application.FileDialog(msoFileDialogFolderPicker)

If myDialog.Show = -1 Then

          myFolder = myDialog.SelectedItems(1) & Application.PathSeparator

          myFile = Dir(myFolder & "*.xls*")

          Do While myFile <> ""

              Workbooks.Open myFolder & myFile

              Workbooks(myFile).Worksheets.Copy After:=ThisWorkbook.ActiveSheet

               Workbooks(myFile).Close

               myFile = Dir

          Loop

End If

End Sub

4 Merge Excel Files Data from a Folder in One File

This VBA code will help you to merge all Excel Files data from a specific folder, into one Excel file.

In the below VBA code, i have given a folder path in “D” Drive, which contains a Folder with the name “Files”. So if your folder name is different then you can change the path according to your drive.

Input Data

Final Output After Merge

VBA CODE

Sub MergeDataFromFolder()

Dim copiedsheetcount As Long

Dim rowcnt As Long

Dim merged As Workbook

Dim wb As Workbook

Dim ws As Worksheet

filefolder = "D:\Files\"

Filename = Dir(filefolder & "*.xlsx")

If Filename = vbNullString Then

MsgBox prompt:="No File", Buttons:=vbCritical, Title:="error"

Exit Sub

End If

copiedsheetcount = 0

rowcnt = 1

Set merged = Workbooks.Add

ActiveSheet.Name = "Merged Data"

Do While Filename <> vbNullString

copiedsheetcount = copiehsheetcount + 1

Set wb = Workbooks.Open(Filename:=filefolder & Filename, UpdateLinks:=False)

Set ws = wb.Worksheets(1)

With ws

If FilterMode Then .ShowAllData

If copiedsheetcount > 1 Then .Rows(1).EntireRow.Delete shift:=xlUp

.Range("a1").CurrentRegion.Copy Destination:=merged.Worksheets(1).Cells(rowcnt, 1)

End With

wb.Close savechanges:=False

rowcnt = Application.WorksheetFunction.CountA(merged.Worksheets(1).Columns("A:A")) + 1

Filename = Dir

Loop

MsgBox prompt:="File Merged", Buttons:=vbInformation, Title:="Success"

End Sub

Merge All Sheets from Multiple Files into a Single Sheet

With the help of this VBA code, you can combine or merge multiple Excel files and their sheets of data into one Excel sheet with one click.

for example, you have 12 months of salary file data and have 3 sheets in each Excel file, in the 1st sheet is Delhi Branch Salary Data, in the 2nd sheet Mumbai Branch Salary Data is given and in the 3rd sheet, Kolkata Branch Salary data is given.

Now i want to combine and merge all the branches’ data with 12 months, which means i want to append 36 sheets of data in one sheet then you should try this code to append all sheets of data into one Excel sheet.

Input Data

Final Output After Merge

VBA CODE

Sub MergeAllFilesAndSheets()

    Dim folderPath As String

    Dim mergedWorkbook As Workbook

    Dim sourceWorkbook As Workbook

    Dim ws As Worksheet

    Dim fileName As String

    Dim mergedSheet As Worksheet

    Dim lastRow As Long

    Dim dataRange As Range

    folderPath = "D:\Files\"

    Set mergedWorkbook = Workbooks.Add

    Set mergedSheet = mergedWorkbook.Sheets(1)

    ' Made by TechGuruPlus.com

    fileName = Dir(folderPath & "*.xlsx")

    Do While fileName <> ""

        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)

        For Each ws In sourceWorkbook.Sheets           

            lastRow = mergedSheet.Cells(mergedSheet.Rows.Count, 1).End(xlUp).Row + 1            

            mergedSheet.Cells(lastRow, 1).Resize(ws.UsedRange.Rows.Count, 1).Value = ws.Name & " (" & sourceWorkbook.Name & ")"            

            ws.UsedRange.Copy Destination:=mergedSheet.Cells(lastRow, 2)

        Next ws        

        sourceWorkbook.Close False 

        fileName = Dir

    Loop   

    mergedWorkbook.SaveAs folderPath & "MergedWorkbook_WithSheetAndFileName.xlsx"

    mergedWorkbook.Close

    MsgBox "All sheets from all files merged successfully", vbInformation

End Sub

Merge Specific Sheets from Multiple Files into a Single Sheet

VBA CODE

Sub MergeSpecificSheetsFromFiles()

    Dim folderPath As String

    Dim mergedWorkbook As Workbook

    Dim sourceWorkbook As Workbook

    Dim ws As Worksheet

    Dim fileName As String

    Dim mergedSheet As Worksheet

    Dim lastRow As Long

    Dim sheetName As String

    Dim sheetFound As Boolean

    Dim mergedFilePath As String

    ' Prompt user to enter the sheet name

    sheetName = InputBox("Enter the sheet name you want to merge:", "Sheet Name")

    If sheetName = "" Then

        MsgBox "No sheet name entered. Exiting...", vbExclamation

        Exit Sub

    End If

    folderPath = "D:\Files\"

    Set mergedWorkbook = Workbooks.Add

    Set mergedSheet = mergedWorkbook.Sheets(1)

    fileName = Dir(folderPath & "*.xls*") ' Handles both .xls and .xlsx extensions

    sheetFound = False

    Do While fileName <> ""

        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)

        On Error Resume Next

        Set ws = sourceWorkbook.Sheets(sheetName)

        On Error GoTo 0

        If Not ws Is Nothing Then

            sheetFound = True

            lastRow = mergedSheet.Cells(mergedSheet.Rows.Count, 1).End(xlUp).Row + 1

            mergedSheet.Cells(lastRow, 1).Resize(ws.UsedRange.Rows.Count, 1).Value = sourceWorkbook.Name

            ws.UsedRange.Copy Destination:=mergedSheet.Cells(lastRow, 2)

        End If

        sourceWorkbook.Close False

        fileName = Dir

    Loop

    If Not sheetFound Then

        MsgBox "This sheet is not found in any file.", vbExclamation

        mergedWorkbook.Close False

        Exit Sub

    End If

    mergedFilePath = folderPath & "MergedWorkbook_" & sheetName & ".xlsx"

    mergedWorkbook.SaveAs mergedFilePath

    mergedWorkbook.Close

    ' Open the merged workbook

    Workbooks.Open mergedFilePath

    MsgBox "All '" & sheetName & "' sheets from all files merged successfully", vbInformation

End Sub

 Merge Specific Sheets from Multiple Workbooks to Separate Tabs 

VBA CODE

Sub MergeSpecificSheetsToSeparateTabs()

    Dim folderPath As String

    Dim sourceWorkbook As Workbook

    Dim mergedWorkbook As Workbook

    Dim ws As Worksheet

    Dim sheetName As String

    Dim fileName As String

    Dim newSheet As Worksheet

    Dim sheetFound As Boolean

    Dim mergedFilePath As String

    ' Prompt user to enter the sheet name

    sheetName = InputBox("Enter the sheet name you want to merge:", "Sheet Name")

    If sheetName = "" Then

        MsgBox "No sheet name entered. Exiting...", vbExclamation

        Exit Sub

    End If

    ' Set the folder path

    folderPath = "D:\Files\" ' You can modify this to your folder path

    ' Create a new workbook to hold the merged sheets

    Set mergedWorkbook = Workbooks.Add

    mergedWorkbook.Sheets(1).Name = "Temp" ' Temporary sheet to delete later

    fileName = Dir(folderPath & "*.xls*") ' Handles both .xls and .xlsx extensions

    sheetFound = False

    Do While fileName <> ""

        Set sourceWorkbook = Workbooks.Open(folderPath & fileName)

        On Error Resume Next

        Set ws = sourceWorkbook.Sheets(sheetName)

        On Error GoTo 0

        If Not ws Is Nothing Then

            sheetFound = True

            ' Add a new sheet to the merged workbook

            Set newSheet = mergedWorkbook.Sheets.Add(After:=mergedWorkbook.Sheets(mergedWorkbook.Sheets.Count))

            newSheet.Name = Left(sourceWorkbook.Name, 31) ' Sheet names are limited to 31 characters

            ' Copy the content of the specific sheet from the source workbook to the new sheet

            ws.UsedRange.Copy Destination:=newSheet.Cells(1, 1)

        End If

        sourceWorkbook.Close False

        fileName = Dir

    Loop

    ' Delete the temporary sheet if at least one sheet was found

    If sheetFound Then

        Application.DisplayAlerts = False

        mergedWorkbook.Sheets("Temp").Delete

        Application.DisplayAlerts = True

    Else

        MsgBox "This sheet is not found in any file.", vbExclamation

        mergedWorkbook.Close False

        Exit Sub

    End If

    ' Save the merged workbook

    mergedFilePath = folderPath & "MergedWorkbook_" & sheetName & ".xlsx"

    mergedWorkbook.SaveAs mergedFilePath

    mergedWorkbook.Close

    ' Open the merged workbook

    Workbooks.Open mergedFilePath

    MsgBox "All '" & sheetName & "' sheets from all files have been merged into separate tabs.", vbInformation

End Sub

Steps to use VBA code to merge Excel sheets data

To enter the above VBA code follow the steps as given below-

1. Go to VBA Application by pressing the shortcut “ALT + F11” or Right click on any sheet name and click on “View Code”.

2. Now Insert a New Module (Go to Insert Menu and Click on Module)

3. Now Copy the above VBA code and Paste in the Blank Module File.

4. Press F5 Button to Run the Macro.

5. All the File data have been merged into one file.

6. if you are facing any problem to merge your data, please write us in the comment box below, and we will find the solution for your query and answer you. Thanks