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

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:

  1. Download the consolidator.xlsm from the link provided at the top and at the bottom of this article.
  2. Place all the excel files, which you want to combine, 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 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.

Files Output

Files Output

7. Output with

  • Remove blank rows – False
  • Remove repeated headers – False
Output - with Repeated Header and blank Rows

Merged File- with Repeated Header and blank Rows

 

8. Output with

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

Merged File- with one Header and deleted blank Rows

Complete 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

Download Link : Consolidator_1.0

 

 

You may also like...

72 Responses

  1. Gunasekaran says:

    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.

  2. Gene Krzywicki says:

    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.

  3. Kumar says:

    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

  4. Peter Samuel says:

    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

  5. Jonny Harper says:

    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

  6. kram says:

    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!

  7. EJ says:

    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,

  8. jz says:

    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!

    • SJ says:

      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.

      • jz says:

        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.

        • SJ says:

          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.

  9. Carlo says:

    Hi! Thank you for this great tool. Can you send me the VBA password? It will be greatly appreciated! Thanks!

  10. ANKUSH says:

    I need to do little modification to use this macro…pls provide me password of vba

  11. AT says:

    Please provide me the password I need to modify it little

  12. Pindasaus says:

    Hi thank you for the tool. Really help me. But do you mind sharing the password?

  13. Suneel says:

    Hi, Great tool, i need to modify little, can you please provide password please

  14. Nelson says:

    hi there,
    this vba works great!! will you provide the password 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 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.

  17. Lc says:

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

  18. Charity says:

    Please send met he password to the vba.
    [email protected]

  19. Jacob says:

    Please send me the password to the vba.

  20. SLJM says:

    Hi, I need to modify the script slightly, would it be possible to obtain the password?

    Many thanks,

    SLJM

  21. Sara says:

    Hi, the tool is very useful

    Could you pls send me the password

  22. Windy says:

    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?

  23. Paparao says:

    Hi SJ…tool is very good. I have little modification to do…can please share the VBA password to me @ [email protected]
    thanks you…. 🙂

  24. andrew says:

    could you please email me the password too

  25. 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

  26. Neville Munda says:

    HI can you provide me the password please. “overflow” error message when I tried to consolidate 15 files or higher. Thanks

  27. ashok says:

    hi buddy!! extremely helpful. can you please share the password of this, i need to make some changes in it.

  28. Emklos says:

    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.

  29. Mani says:

    Hi, Please provide the Password please, I need to modify the code little bit to fulfil my need.
    Thanks

  30. vishal says:

    i i want to make changes pls provide mne apsswrod

  31. Meng says:

    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 🙂

  32. Christian says:

    Hello, one more thanking you for your efforts, could you provide pss to do modifications needed? Thanks in Advance, BR

  33. Sagar Kochar says:

    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

  34. Sagar says:

    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

  35. Coco says:

    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.

  36. Omer says:

    send me could you please email me with the pass­word? I need to make a few changes on VBA.
    [email protected]

  37. Chinmay says:

    Thanks for great code but requesting you to share password i want to make changes in code.

  38. Rusty says:

    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

Leave a Reply

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

%d bloggers like this: