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
Public Sub ConsolidateSheets()
Dim i As Integer
Sheets.Add
ActiveSheet.Name = "Merged Data"
For i = 1 To Worksheets.Count
Worksheets(i).Select
Range("a2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Worksheets("Merged Data").Select
Range("A1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Next i
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
How to use the above VBA code to merge excel sheets data
To enter the above VBA code follow the steps as given below-
- Go to VBA Application by pressing the shortcut “ALT + F11” or Right click on any sheet name and click on “View Code”.
- Now Insert a New Module (Go to Insert Menu and Click on Module)
- Now Copy the above VBA code and Paste in the Blank Module File.
- Press F5 Button to Run the Macro.
- All the File data have been merged into one file.
- 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
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 .
Hi Sir,
Please help in providing Vba code to merge data from multiple
Files with source file name in merged file against each row
Need to merge six excel workbook with each containing two Sheet in it in one excel workbook with two sheet
How to merge a specific sheet (eg- sheet4)from all the excel files. We need only sheet4 from all the workbooks.
how can i merge xlm file
Excellent Sir
Thanks Sandeep Ji
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.
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.
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