If you’ve got something to work on with several excel files. More than 150 excel files generated by a web based application have to be merged into one file and then create a summary from it. A simple way to combine or merge multiple excel files, with macros in Excel.
If you’re not familiar with office macros, do not worry you can use it easily by just write a simple xls file merger code on vb editor, change the working folder path and cell starter reference name inside the code to suit your reference, and then click Run Sub. All excel (xls or xlsx) files inside working folder will be merged into current worksheet.
Download the file here: MergeWorkBooks_ExampleFile.xls
Also read How to Unprotect an Excel Sheet Without Password
For more detail, here’s is a step-by-step intro.
- Gather all xls or xlsx files that you wanted to merge into a folder. Remember that this merger macros will only grab the first worksheet on spreadsheet files. So make sure that all contents is on the first worksheet before continue.
- Close all working excel files so you can focus only on merging files.
- On MS Excel, create new spreadsheet by simply pressing CTRL+N.
- And open Microsoft Visual Basic editor by pressing ALT+F11, you’ll see a blank text editor.
- Now open by doubleclicking ThisWorkBook on the left sheet menu.
- Paste the following macros code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("D:changetoexcelfilespathhere")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
|
- Change the folder as mentioned on comment on the macros code
- Change also column start reference to suit your need (usually first row used by column header, so i used A2 as start point).
- For example to start merging all files from column “B” row “1”.
- Change “IV” only if you have files using column wider than column “IV”. Basically, it will try to copy values on all available columns. If you notice the latest column on new worksheet is “IV”, it is the default available column on until your columns growth more than that.
Range("B1:IV" & Range("B65536").End(xlUp).Row).Copy
|
- If everything configured already, press “F5″ or click on play icon to run the code (RunSub). You’ll see working progress on left sheet menu.
- If all done, you can now switch to worksheet to see the result.
If you set a new folder within the code, and then hit “F5″ or press SubRun button, the result will be added into current worksheet bellow the previous data. That’s mean it will not overwritten the last result but as another merge to previous merged data. In conclusion, you can spam change folder path and hit F5 to run the code in order to merge all files on provided folder into current worksheet. If you want to start new merge for multiple xls or xlsx files you have to clear the current worksheet, or create new file for merge. I hope there’s also a way to merge spreadsheet similar to this but for LibreOffice, since i also work on several ods files.
Also read
- Consolidate Data from Multiple Worksheets into a Master Worksheet in Excel using VBA
- 5 must-know Excel macros for common tasks
- Understanding Variables, Conditions & Loops in Excel VBA
Courtesy: oaultimate.com
Thanks a lot for the code. I am looking to consolidate the data from different sheets to one sheet. How can we do that.
Thank you for this code. Are you able to advise how to amend this code so that it give the options for users to select the folder location, instead of having to type it into the vba? Thank you
With al do respect but isnt Power Query the prefererend wat to tackle thuis nowadays? Or is there a good argument why PQ is nog suitable for the job?
I believe the latest excel versions have a function called consolidate to help with that.
Hi when I try to run, it prompts me to type in a macro. what do I type in then?
F
Thank you for this vba code, but I have a problem when I run de code, I get every time a popup for each sheet that says do you want to save or delete.
What is wrong I have exel 2016 running.
Sub test()
Dim fn, ws As Worksheet, e, flg As Boolean, LastR As Range, wsName As String
‘Set dirObj = mergeObj.Getfolder(“D:\EXCEL\TEST_FILES”)
fn = Application.GetOpenFilename(“Excel(*.xls*),*.xls*”, MultiSelect:=True)
If Not IsArray(fn) Then Exit Sub
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Application.ScreenUpdating = False
ws.Cells.Clear
For Each e In fn
With Workbooks.Open(e)
With .Sheets(1)
wsName = .Name
If Not flg Then
.Rows(1).Copy ws.Cells(1)
ws.Columns(1).Insert
ws.Cells(1).Value = “SR_NUMBERS”
flg = True
End If
Set LastR = ws.Cells(Rows.Count, 2).End(xlUp)(2)
With .Range(“a1”).CurrentRegion
With .Resize(.Rows.Count – 1).Offset(1)
.Copy LastR
LastR(, 0).Resize(.Rows.Count).Value = CreateObject(“Scripting.FileSystemObject”).GetBasename(e)
End With
End With
End With
.Close False
End With
Next
ws.Range(“a1”).CurrentRegion.Columns.AutoFit
Application.ScreenUpdating = True
Set ws = Nothing
Columns(“B:C”).Select
Selection.Delete Shift:=xlToLeft
Range(“B1”).Select
End Sub
Thank for Code.. Its very helpful !!
Further to this I have multiple sheets in My workbook and I want to consolidate all data in “Sheet5” of each workbook(excel) into one file as above.
Kindly help on this.
Thanks again !!!
change this line
ThisWorkbook.Worksheets(1).Activate
if the sheet name is fixed
ThisWorkbook.Worksheets(“Sheet5”).Activate
if the sheet position is fixed
ThisWorkbook.Worksheets(5).Activate
Hope that helps
Thank you for input,
It gives me error as Subscript out of Range, Please help to change sheet and also I require only few columns to be selected and copied, Is that possible ?
Thank you for input, Followed same but I am receiving “subscript out of range”,
Also I would require only selected columns to be copied into new file, Is that possible ?
Hi Raghu R. Your recommendations only allows for the data to be written to a specific sheet (destination sheet). Are you able to comment on how to copy a specific sheet from the source. Thanks
please check this link
Did you manage to find a way to do this? If so please could you advise? Much appreciated
Thanks for the code. It works perfectly.
We’re a group of volunteers and opening a new scheme in our community.
Your site provided us with valuable info to work on. You’ve
done an impressive job and our entire community will be thankful to you.
Thank you for input, Followed same but I am receiving “subscript out of range”,
Also I would require only selected columns to be copied into new file, Is that possible ?
Thanks for the code! Worked like a charm and now I have time to get other things accomplished.
You are a life saver! Is there a way to incorporate the file name of the files we merge as well please?
The code works very nice with Excel files. How can I do the same with CSV files? All my CSV files are within a single folder, and need to merge them to a single CSV file.
When i use this code, i get an error, Run-time error ‘424’: Object Required. Can you please help
Hi.. This code helped loads.. Now I’m trying to consolidate Sheet1 from all files into Sheet1 of Master file and Sheet2 from all files into Sheet2 of Master File. Please help