Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA-Excel: Consolidator – Merge or Combine Multiple Excel Files Into One

Down­load Link : Consolidator_1.0

If you want to com­bine many excel files into one file and you don’t want to do it man­u­ally, you have come to the right place. This piece of soft­ware will allow you merge as many excel files you want, say 500‑1000 excel files. This tool will pro­vide you var­i­ous options of for­mat­ting before you merge your files.

How to Use it:

  1. Down­load the consolidator.xlsm from the link pro­vided at the top and at the bot­tom of this article.
  2. Place all the excel files, which you want to com­bine, into one folder (make sure all files are closed).

Excel Files

Excel Files

     3. Open the Consolidator.xlsm.

     4. Go to the sheet 2(“Change Path and header set­tings”). In this sheet you have options to–

  • Remove blank rows” if avail­able in any of this files while combining.
  • Remove repeated head­ers” if avail­able in any of this files while combining.
  •  Pro­vide the folder path where you all excel files are stored.

   5. Once done with set­ting , you click the “Click to com­bine files” either from sheet 1(“List of Files“) or Sheet 2 (“Change Path and header set­tings”)

   6. That’s it, your all the files from the spec­i­fied folder will be com­bined into one and gets stored in to Sheet 3(“Combine”). At the end you will get a mes­sage box stat­ing how many files are com­bined and name of all the files com­bined in sheet 1.

Files Output

Files Out­put

7. Out­put with

  • Remove blank rows – False
  • Remove repeated head­ers – False
Output - with Repeated Header and blank Rows

Merged File– with Repeated Header and blank Rows

 

8. Out­put with

  • Remove blank rows – True
  • Remove repeated head­ers – True
Merged File- with one Header and deleted blank Rows

Merged File– with one Header and deleted blank Rows

Com­plete Code:

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

Down­load Link : Consolidator_1.0

 

 

You may also like...

72 Responses

  1. Gunasekaran says:

    This web­site looks great. It seems this web­site ful­fill all my needs. Step by steps codes giv­ing good under­stand­ing to the users. As a learner I would rate 9 out of 10. If you share the pass­word for the attached file then I would rate 10 out of 10. Once again thanks.

  2. Gene Krzywicki says:

    Thank you for shar­ing this great util­ity. I’ve used it to com­bine many indi­vid­ual txt files into one. One prob­lem I dis­cov­ered that i have is that, some­times I get a 12 digit num­ber. After the files are com­bined this field looses the orig­i­nal data and is replaced with a trun­cated entry (123450000000)).

    Cur­rently I look for these 12 digit entries and add them manually.

    Are than any changes that I could make to the global vari­ables that might acco­modi­ate these numbers?

    Thank you.

  3. Kumar says:

    I have 3 dif­fer­ent excel file in which header are same ‚but order of head­ers are dif­fer­ent in 3 excel files ‚Exam­ple in one excel file header name is PHF No. which is in Col­umn A ‚the same header in 2 excel file which is col­umn D,the same header in 3 excel file which is col­umn F,

    my QUERY is While merg­ing it is not tak­ing due to dif­fer­ent columns hav­ing same headers

    Can you help me out in merg­ing the 3 excel which are same in header but there pres­ence in dif­fer­ent coulmns ‚and at the same time while merg­ing them dupli­cate should be deleted automatically

    Pls help me and if pos­si­ble pls send sam­ple excel of it

  4. Peter Samuel says:

    Excel­lent work buddy. while work­ing on my project, bumped into your tool. so much inline with what i need. pos­si­ble for you to share the pass­word of this VBA code com­po­nent ? need to cus­tomize few things . you can send it to my email if shar­ing here is not what you like to do.

    Cheers
    PS

  5. Jonny Harper says:

    Thank you for this SJ, it’s prov­ing to be extremely use­ful for me! Is it pos­si­ble to make the com­bine include files within sub-folders?
    Cheers
    Jonny

    • SJ says:

      Yes in that case you need to check all the sub­fold­ers, put it in loop, and inside the loop use the exist­ing code 🙂

  6. kram says:

    Hello SJ! Your work is amaz­ing but i just need to edit some things to fit my needs, is it ok if you also share the pass­word with me? Thanks in advance!

  7. EJ says:

    Great tool. Exactly what I’ve looked for.
    I have 2 ques­tions:
    1. In my work­book I have two head­ers rows. Is it pos­si­ble to remove both of them when more then 1 file?
    2. Can you please share the pass­word with me? I would like to edit few things.

    Thanks in advance,

  8. jz says:

    Hi, it is great tool. I have one ques­tion:
    My each excel file has two sheets: sheet1 ‚sheet2, how to com­bine 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!

    • SJ says:

      Thanks for appre­ci­a­tion,
      You need to make few changes in the code.
      1. copy the data from sheet1 and put it in sheet1 of new file, exist­ing code will do that for you.
      2. for sheet2, before you copy the data, acti­vate the sheet2 ex: Workbook.Sheets(“Sheet2”).UsedRange.Copy and before you paste the data, acti­vate the sheet2 in the des­ti­na­tion excel file.(I do not think that you need to acti­vate the sheet2 in des­ti­na­tion excel file every time.)
      3. Exist­ing code doesn’t not put the com­bined data in new file, so you can run the code twice for sheet1 and sheet2 after mak­ing changes men­tioned in first 2 steps and man­u­ally copy the com­bined data into new file
      do let me know if you need fur­ther help.

      • jz says:

        Thanks for your reply. I am not very clear about the sec­ond step. I have over 50 excle files,each has sheet2. How to acti­vate them? I appre­ci­ate your help.

        • SJ says:

          it should be mainWorkBook.Sheets(“Sheet2”).Activate OR try copy­ing directly from sheet2 mainWorkBook.Sheets(“Sheet2”).UsedRange.Copy
          if you still face prob­lem, let me know, cur­rently busy but i will try to pro­vide the solu­tion on weekend.

  9. Carlo says:

    Hi! Thank you for this great tool. Can you send me the VBA pass­word? It will be greatly appre­ci­ated! Thanks!

  10. ANKUSH says:

    I need to do lit­tle mod­i­fi­ca­tion to use this macro…pls pro­vide me pass­word of vba

  11. AT says:

    Please pro­vide me the pass­word I need to mod­ify it little

  12. Pindasaus says:

    Hi thank you for the tool. Really help me. But do you mind shar­ing the password?

  13. Suneel says:

    Hi, Great tool, i need to mod­ify lit­tle, can you please pro­vide pass­word please

  14. Nelson says:

    hi there,
    this vba works great!! will you pro­vide the pass­word to me please? thanks!

  15. ANKUSH says:

    I need to do lit­tle mod­i­fi­ca­tion to use this macro…pls pro­vide me pass­word of vba

  16. Anna says:

    I am get­ting an Over­flow error when con­sol­i­dat­ing. I need to com­bine 10 files with about 20,000 rows on each file. Is this some­thing that can be fixed on the code? I hope you can share with me the password.

  17. Lc says:

    will you pro­vide the pass­word to me please? thanks!

  18. Jacob says:

    Please send me the pass­word to the vba.

  19. SLJM says:

    Hi, I need to mod­ify the script slightly, would it be pos­si­ble to obtain the password?

    Many thanks,

    SLJM

  20. Sara says:

    Hi, the tool is very useful

    Could you pls send me the password

  21. Windy says:

    Hi, great tools. but for the blank row could it be included those for­mula blank row ?
    I mean For­mula on the cell, but the result was blank. pos­si­ble excluded as well?

  22. andrew says:

    could you please email me the pass­word too

  23. chaitanya says:

    I am get­ting an Over­flow error when con­sol­i­dat­ing. I need to com­bine 100 files with about 3000 rows on each file. Is this some­thing that can be fixed on the code? I hope you can share with me the password

  24. Neville Munda says:

    HI can you pro­vide me the pass­word please. “over­flow” error mes­sage when I tried to con­sol­i­date 15 files or higher. Thanks

  25. ashok says:

    hi buddy!! extremely help­ful. can you please share the pass­word of this, i need to make some changes in it.

  26. Mani says:

    Hi, Please pro­vide the Pass­word please, I need to mod­ify the code lit­tle bit to ful­fil my need.
    Thanks

  27. vishal says:

    i i want to make changes pls pro­vide mne apsswrod

  28. Christian says:

    Hello, one more thank­ing you for your efforts, could you pro­vide pss to do mod­i­fi­ca­tions needed? Thanks in Advance, BR

  29. Sagar Kochar says:

    Hey Great Stuff SJ !!! You really saved me a lot of time, i was work­ing on the same lines since the last two days but was encoun­ter­ing some issues with my code needed to con­sol­i­date like 100 .csv files, this thing is just what I was look­ing for !!!!

    Could you pro­vide me the pass­word i’d like to make a few changes to adapt it to work? 🙂 Thanks in Advance, Sagar

  30. Coco says:

    Hi SJ,

    This tool is very use­ful. It helped me a lot in my projects. Im still mak­ing some for­mat mod­i­fi­ca­tions in the com­bined files though. But over­all, this is really a time saver. Could you please send me the pw, i would like to make a few mod­i­fi­ca­tions to address some of the for­mat issues. Thanks in advance.

  31. Chinmay says:

    Thanks for great code but request­ing you to share pass­word i want to make changes in code.

  32. Rusty says:

    Hi
    Using this very use­ful code but I need to mod­ify it. Can you please send me the pass­word as many oth­ers have also requested. Just curi­ous, why pro­tect with a pass­word as so many posts here are request­ing it? Please do not pub­lish my email address.
    Many thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: