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

1 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 Consolidate Excel All Sheets Data in One File

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 Data 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


5 Merge Excel Files Into One (with All Sheet’s Data)

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

Append Excel files into one Excel

Final Output After Merge

Merged Big Excel Data

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

Download Files

Tutorial Video


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
Join Our Telegram Group techguruplus telegram group Join Our WhatsApp Group techguruplus whatsapp group
Nazim Khan - Author Image

Nazim Khan (Author) 📞 +91 9536250020
[MBA in Finance]

Nazim Khan is an expert in Microsoft Excel. He teaches people how to use it better. He has been doing this for more than ten years. He is running this website (TechGuruPlus.com) and a YouTube channel called "Business Excel" since 2016. He shares useful tips from his own experiences to help others improve their Excel skills and careers.

18 thoughts on “Combine | Merge Multiple Excel File (5 Ways) All Data into One with VBA Code”

  1. Dear sir,
    Kindly support. I want to select folder in formula num 4 as like code number 3 .

    What will be my code is . pls help .

    Reply
  2. Hi Sir,
    Please help in providing Vba code to merge data from multiple
    Files with source file name in merged file against each row

    Reply
  3. Need to merge six excel workbook with each containing two Sheet in it in one excel workbook with two sheet

    Reply
  4. How to merge a specific sheet (eg- sheet4)from all the excel files. We need only sheet4 from all the workbooks.

    Reply
  5. This was excelent code for me. But I want to merge diffrent type pattern data in one excel (like overtime attendance and without overtime attendance, diffrent department attendance, one excel has EMP code data and other doesn’t have EMP code) this code is overloading sometimes after merge data. After implementing this I need to do more work. If any suggetion please provide. I am normal salaried employe. I have that much amount for perchase your courses. but I want to learn.

    Reply
  6. Hello Sir, I have used you 4th code. This code collects full data when there is no empty row in between the data. If there is any empty row in between the data the code move to the next file.

    Reply
    • Yes Vijay Ji, there should be no blank row in your data, but i m finding new code which can collect all data whether it contains blank rows, but it will take times, when i find the code, i will upload on this page, and also made a video on my YouTube channel.

      Thanks

  7. Hi, this is super helpful, thanks for sharing this openly with everyone. I have a excel problem and wondering if you ca help solve that. If I have specific tabs from multiple excels, how can I export the in one excel.

    Reply
  8. can you please provide the code to consolidate the multiple excel file with multiple sheets . data should be consolidate in respective sheet.

    Reply
  9. If I have 2 workbookand 1st workbook contain 1,2 and 3 and sheets and 2nd workbook contain 2,3 and 1 sheets. Now I want I single workbook which merge data of 1,2 and 3 sheets from both workbooks in each separate sheet. New workbook should have three sheets (1,2 and3) and each sheets example sheet 1 have data of both workbook, then sheet 2 have data of both workbook and so on. Which formula I can use for this.

    Reply
  10. HI
    sir
    kindly make a totruil
    i have many excel files and every sheet have many sheet how to create master sheet of all sheets and same item are not repeated and link master file to all files if i put the value in master file this value automatic put all the sheet where this itom are located plz sir

    Reply

Leave a Comment