Download Link : Consolidator_1.0
If you want to combine many excel files into one file and you don’t want to do it manually, you have come to the right place. This piece of software will allow you merge as many excel files you want, say 500-1000 excel files. This tool will provide you various options of formatting before you merge your files.
How to Use it:
- Download the consolidator.xlsm from the link provided at the top and at the bottom of this article.
- Place all the excel files, which you want to combine, into one folder (make sure all files are closed).

3. Open the Consolidator.xlsm.
4. Go to the sheet 2(“Change Path and header settings”). In this sheet you have options to-
- “Remove blank rows” if available in any of this files while combining.
- “Remove repeated headers” if available in any of this files while combining.
- Provide the folder path where you all excel files are stored.
5. Once done with setting , you click the “Click to combine files” either from sheet 1(“List of Files“) or Sheet 2 (“Change Path and header settings”)
6. That’s it, your all the files from the specified folder will be combined into one and gets stored in to Sheet 3(“Combine”). At the end you will get a message box stating how many files are combined and name of all the files combined in sheet 1.

7. Output with
- Remove blank rows – False
- Remove repeated headers – False

8. Output with
- Remove blank rows – True
- Remove repeated headers – True

Complete Code:
[sourcecode language=”VB”]
Sub sumit()
Dim fso As New FileSystemObject
Dim NoOfFiles As Double
Dim counter As Integer
Dim r_counter As Integer
Dim s As String
Dim listfiles As Files
Dim newfile As Worksheet
Dim mainworkbook As Workbook
Dim combinedworksheet As Worksheet
Dim tempworkbook As Workbook
Dim rowcounter As Double
Dim rowpasted As Integer
Dim delHeaderRow As Integer
Dim Folderpath As Variant
Dim headerset As Variant
Dim Actualrowcount As Double
Dim x As Long
Dim Delete_Remove_Blank_Rows As String
Folderpath = ActiveWorkbook.Sheets(2).Range("I7").Value
headerset = ActiveWorkbook.Sheets(2).Range("F4").Value
Delete_Remove_Blank_Rows = ActiveWorkbook.Sheets(2).Range("F3").Value
NoOfFiles = fso.GetFolder(Folderpath).Files.Count
Dim Files_Count_No_Of_Rows_In_Sheets(1000) As Double ‘declare the array of the size of no of files in the folder
Set listfiles = fso.GetFolder(Folderpath).Files
counter = 0
r_counter = 1
rowcounter = 1
Actualrowcount = 0
For Each fls In listfiles
counter = counter + 1
Range("A" & counter).Value = fls.Name
Next
‘MsgBox ("count of files in folder is " & NoOfFiles)
Set mainworkbook = ActiveWorkbook
Set combinedworksheet = mainworkbook.Sheets(2)
mainworkbook.Sheets(3).UsedRange.Clear
‘MsgBox ("Sheet is clear for the data to be copied")
For i = 1 To NoOfFiles
mainworkbook.Sheets("Combine").Activate
‘MsgBox ("Sheet 3 is Activated")
mainworkbook.Sheets("Combine").Range("A" & rowcounter).Select
Application.Workbooks.Open (Folderpath & "\" & Range("A" & i).Value)
Set tempworkbook = ActiveWorkbook
Set newfile = ActiveSheet
rowpasted = rowcounter
‘MsgBox ("pointer at " & rowpasted)
newfile.UsedRange.Copy
‘MsgBox ("Data is copied")
mainworkbook.Sheets(3).Paste
‘MsgBox ("Data is pasted successfully")
‘MsgBox ("Blank rows has been deleted " & Remove_Blank_Rows & " " & headerset)
If Delete_Remove_Blank_Rows = "Yes" Then
‘If Remove_Blank_Rows = Yes Then
‘MsgBox ("Blank rows has been deleted" & Delete_Remove_Blank_Rows)
For x = mainworkbook.Sheets("Combine").Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(mainworkbook.Sheets("Combine").Rows(x)) = 0 Then
mainworkbook.Sheets("Combine").Rows(x).Delete
‘MsgBox ("Blank rows has been deleted" & Remove_Blank_Rows)
End If
Next
End If
rowcounter = mainworkbook.Sheets(3).UsedRange.Rows.Count + 1
‘MsgBox ("row counter is updated" & rowcounter)
rowpasted = rowcounter – rowpasted
‘MsgBox ("No fo rows pasted" & rowpasted)
delHeaderRow = rowcounter – rowpasted
‘MsgBox ("Which row to delete" & delHeaderRow)
‘MsgBox ("Pointer at row beforw deletion" & rowpasted)
If headerset = "Yes" Or headerset = "YES" Or headerset = "yes" Then
If delHeaderRow <> 1 Then
mainworkbook.Sheets(3).Rows(delHeaderRow).EntireRow.Delete
rowcounter = rowcounter – 1
rowpasted = rowpasted – 1
Else
End If
Else
End If
‘MsgBox ("Header deleted")
‘MsgBox ("row counter is updated" & rowcounter)
combinedworksheet.UsedRange.ClearOutline
‘combinedworksheet.
tempworkbook.Close
‘MsgBox ("no of rows are abt to get pasted in sheet 1")
Files_Count_No_Of_Rows_In_Sheets(i) = rowpasted
Actualrowcount = Actualrowcount + rowpasted
Next i
mainworkbook.Sheets(1).UsedRange.ClearContents
For Each fls In listfiles
r_counter = r_counter + 1
mainworkbook.Sheets(1).Range("A" & r_counter).Value = fls.Name
mainworkbook.Sheets(1).Range("B" & r_counter).Value = Files_Count_No_Of_Rows_In_Sheets(r_counter – 1)
mainworkbook.Sheets(1).Range("A" & r_counter, "B" & r_counter).Borders.Value = 1
Next
mainworkbook.Sheets(1).Range("B" & r_counter + 1).Interior.ColorIndex = 46
mainworkbook.Sheets(1).Range("B" & r_counter + 1).Value = Actualrowcount
mainworkbook.Sheets(1).Range("B" & r_counter + 1).Borders.Value = 1
mainworkbook.Sheets(1).Range("A1", "B1").Interior.ColorIndex = 46
mainworkbook.Sheets(1).Range("A1", "B1").Borders.Value = 1
mainworkbook.Sheets(1).Range("A1").Value = "Files List"
mainworkbook.Sheets(1).Range("B1").Value = "No Of Rows"
MsgBox ("List of Files are Availabe in sheet 1..Total " & NoOfFiles & " Files Combiled")
End Sub
[/sourcecode]
Download Link : Consolidator_1.0
This website looks great. It seems this website fulfill all my needs. Step by steps codes giving good understanding to the users. As a learner I would rate 9 out of 10. If you share the password for the attached file then I would rate 10 out of 10. Once again thanks.
Thank you for sharing this great utility. I’ve used it to combine many individual txt files into one. One problem I discovered that i have is that, sometimes I get a 12 digit number. After the files are combined this field looses the original data and is replaced with a truncated entry (123450000000)).
Currently I look for these 12 digit entries and add them manually.
Are than any changes that I could make to the global variables that might accomodiate these numbers?
Thank you.
I have 3 different excel file in which header are same ,but order of headers are different in 3 excel files ,Example in one excel file header name is PHF No. which is in Column A ,the same header in 2 excel file which is column D,the same header in 3 excel file which is column F,
my QUERY is While merging it is not taking due to different columns having same headers
Can you help me out in merging the 3 excel which are same in header but there presence in different coulmns ,and at the same time while merging them duplicate should be deleted automatically
Pls help me and if possible pls send sample excel of it
Kumar
Please refer this link,
http://excel-macro.tutorialhorizon.com/vba-excel-modified-consolidator-merge-or-combine-multiple-excel-files-into-one-where-columns-are-not-in-order/
Soon i will write it on delete the duplicate rows
Sumit
Excellent work buddy. while working on my project, bumped into your tool. so much inline with what i need. possible for you to share the password of this VBA code component ? need to customize few things . you can send it to my email if sharing here is not what you like to do.
Cheers
PS
Sent the password to your mail.
Thanks SJ
Hello peter,
Can you please forward me that unprotected vba file to my email id. I need that to customize few things. It would be a great help for me.. email: [email protected]
Regards
Mukund
If you share the password also, It will be great. Please!!
Regards
Mukund
sent
Thank you for this SJ, it’s proving to be extremely useful for me! Is it possible to make the combine include files within sub-folders?
Cheers
Jonny
Yes in that case you need to check all the subfolders, put it in loop, and inside the loop use the existing code 🙂
Hello SJ! Your work is amazing but i just need to edit some things to fit my needs, is it ok if you also share the password with me? Thanks in advance!
Still looking for it???
Great tool. Exactly what I’ve looked for.
I have 2 questions:
1. In my workbook I have two headers rows. Is it possible to remove both of them when more then 1 file?
2. Can you please share the password with me? I would like to edit few things.
Thanks in advance,
Sent you 🙂
Thanks. Any idea how can I remove the second header as well?
Hi, it is great tool. I have one question:
My each excel file has two sheets: sheet1 ,sheet2, how to combine them into one new excel file but keep all sheet1 in new excel sheet1 and sheet2 in new excel sheet2? Is it clear? Thanks in advance!
Thanks for appreciation,
You need to make few changes in the code.
1. copy the data from sheet1 and put it in sheet1 of new file, existing code will do that for you.
2. for sheet2, before you copy the data, activate the sheet2 ex: Workbook.Sheets(“Sheet2”).UsedRange.Copy and before you paste the data, activate the sheet2 in the destination excel file.(I do not think that you need to activate the sheet2 in destination excel file every time.)
3. Existing code doesn’t not put the combined data in new file, so you can run the code twice for sheet1 and sheet2 after making changes mentioned in first 2 steps and manually copy the combined data into new file
do let me know if you need further help.
Thanks for your reply. I am not very clear about the second step. I have over 50 excle files,each has sheet2. How to activate them? I appreciate your help.
it should be mainWorkBook.Sheets(“Sheet2”).Activate OR try copying directly from sheet2 mainWorkBook.Sheets(“Sheet2”).UsedRange.Copy
if you still face problem, let me know, currently busy but i will try to provide the solution on weekend.
Hi! Thank you for this great tool. Can you send me the VBA password? It will be greatly appreciated! Thanks!
I need to do little modification to use this macro…pls provide me password of vba
Mailed u the password
Please provide me the password I need to modify it little
Sent at your mail id. Good luck.
Hi thank you for the tool. Really help me. But do you mind sharing the password?
Hi, Great tool, i need to modify little, can you please provide password please
hi there,
this vba works great!! will you provide the password to me please? thanks!
mailed you.
I need to do little modification to use this macro…pls provide me password of vba
I am getting an Overflow error when consolidating. I need to combine 10 files with about 20,000 rows on each file. Is this something that can be fixed on the code? I hope you can share with me the password.
Password has been sent to you at you email
will you provide the password to me please? thanks!
Please send met he password to the vba.
[email protected]
Password has been sent to you at you email
Password has been sent to you at you email
Please send me the password to the vba.
mailed you 🙂
Hi, I need to modify the script slightly, would it be possible to obtain the password?
Many thanks,
SLJM
mailed you.
Hi, the tool is very useful
Could you pls send me the password
mailed it.
Hi, great tools. but for the blank row could it be included those formula blank row ?
I mean Formula on the cell, but the result was blank. possible excluded as well?
yes, in the function check if cell id blank, skip it
Hi SJ…tool is very good. I have little modification to do…can please share the VBA password to me @ [email protected]
thanks you…. 🙂
mailed you.
could you please email me the password too
sent you
I am getting an Overflow error when consolidating. I need to combine 100 files with about 3000 rows on each file. Is this something that can be fixed on the code? I hope you can share with me the password
mailed you
Hi Chaitanya,
Were you able to resolve the overflow error ? Im getting the same thing, need to consolidate like 100 files with upto 40,000 data rows in some of them. Could you help me out with the solution ? My mail ID is [email protected]
sent u the password
HI can you provide me the password please. “overflow” error message when I tried to consolidate 15 files or higher. Thanks
mailed you
hi buddy!! extremely helpful. can you please share the password of this, i need to make some changes in it.
sent
Hi, this is exactly what I needed. I run into some problems while using the code provided in this post (specifically with removing the headers part which i’m unable to fix on my own). Is it possible to send me file password? [email protected] thanks.
Hi, Please provide the Password please, I need to modify the code little bit to fulfil my need.
Thanks
sent
i i want to make changes pls provide mne apsswrod
sent
very useful tool for my project! Many thanks.
could you please email me with the password? I need to make a few changes on VBA.
[email protected]
thanks again 🙂
Hello, one more thanking you for your efforts, could you provide pss to do modifications needed? Thanks in Advance, BR
sent
Hey Great Stuff SJ !!! You really saved me a lot of time, i was working on the same lines since the last two days but was encountering some issues with my code needed to consolidate like 100 .csv files, this thing is just what I was looking for !!!!
Could you provide me the password i’d like to make a few changes to adapt it to work? 🙂 Thanks in Advance, Sagar
Hi SJ,
Thanks for the wonderful work you have done. However I am receiving Overflow Error message could you please mail me the password at [email protected], so that I can see if there is a solution to resolve this issue.
Thanks & Regards,
Sagar Kochar
sent
Hi SJ,
This tool is very useful. It helped me a lot in my projects. Im still making some format modifications in the combined files though. But overall, this is really a time saver. Could you please send me the pw, i would like to make a few modifications to address some of the format issues. Thanks in advance.
send me could you please email me with the password? I need to make a few changes on VBA.
[email protected]
Thanks for great code but requesting you to share password i want to make changes in code.
Hi
Using this very useful code but I need to modify it. Can you please send me the password as many others have also requested. Just curious, why protect with a password as so many posts here are requesting it? Please do not publish my email address.
Many thanks in advance