Merge multiple excel files into a single spreadsheet (MS Excel 2007)

Last updated on November 11, 2013 @ 39 Comments

Recently I’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. Usually i did this by doing copy paste all values one at a time into a new empty spreadsheet, or copy to merge sheets to another excel files one by one. But yesterday my friend show me a simple way to combine or merge multiple excel files with macros inside Excel 2007 (edit macros with MS Visual Basic editor and no download required).

Although I’m not familiar with office macros, but i 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 RunSub. All excel (xls or xlsx) files inside working folder will be merged into current worksheet.

Watch it on YouTube
For more detail, here’s what i did to merge multiple excel files with MS Excel 2007.

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

Gather all xls or xlsx files

  • 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:
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:\change\to\excel\files\path\here")
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
'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
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.

Simple way to merge multiple excel files into a single spreadsheet

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.


Read also merge excel with simple merger tool for small files — merge without macros.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

39 Comments → “Merge multiple excel files into a single spreadsheet (MS Excel 2007)”

  1. Emmi

    What if the sheets I want to consolidate is in Sheet3 with Sheet Name “FT DEBITS”? Is it possible that this sheet will be the one to be merged instead of the first sheet?

    • M Riza

      Hi Emmi,
      For example you want to merge Sheet3, set active Sheet 3 using the following code:


      Or if you want to use sheet name instead:

      bookList.Worksheets("FT DEBITS").Activate

      Put after/bellow the following code (line 11).

      Set bookList = Workbooks.Open(everyObj)
      bookList.Worksheets("FT DEBITS").Activate
      • Emmi

        Hi M Riza,
        Thank you very much for this. I’m now able to generate the report I exactly wanted without spending so much time. 🙂 Your article is really a big help. Thank you. 🙂

      • M Riza

        You’re welcome.
        I am glad to be of help, have a nice day.

  2. Sreedhar

    Its saved my lot of time…Thank you very much for posting this article….!

    • M Riza

      Hi, You’re welcome, i’m happy to have been of help. Thanks also for visiting my blog.

  3. Chinh

    What if I want to copy one column from one workbook to another and place them next to each other, as opposed to on top of each other. For example, comlumn B in workbook 1 to column C of the new workbook, column B of workbook 2 to column D of the new workbook, column B of workbook 3 to Column E of the new workbook, and so on.

    • M Riza

      Hi, I’m not sure if this is what you mean, but try this

      Set a new variable for number of columns you have on sources files.
      Write the following lines before loop

      Dim i
      i = 0

      And then change

      Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial


      Range("A1").Offset(0, i).PasteSpecial

      increase i value with number of columns you have on source xls files (for example you have 9 columns on sources files)

      i = i + 9

      So the script will be:

      Dim i
      i = 0
      For Each everyObj In filesObj
      Range("A1").Offset(0, i).PasteSpecial
      i = i + 9
      Application.CutCopyMode = False
  4. dacaprice

    Nice work! What if I would like a new sheet for every file I import?

    • M Riza

      Hi, i see you’re familiar with coding so, please try this:
      Add new variable as Object before loop (for example WSA)

      Dim WSA as Object

      On the loop set WSA to add new sheet and then set activate

      Set WSA = ThisWorkbook.Worksheets.Add
      • kyle

        i am unable to get this to work could you be more specific?

  5. Isa

    Hi, I tried using this code to merge 50 files (all same format) into a sheet- the macro is working through all the files, I can see that, but in the end there is no data- can you help? where am I going wrong? Many thanks

    • M Riza

      Hi, please make sure you set the right range for source and destination cells on the macros. Read on comments lines for more detail.

  6. Chris

    Script is awesome, thank you. However, it gets through about 20 of the 111 files and then gives me a “1004” Runtime Error. Any ideas why?


    • M Riza

      Hi, is there any message shown after runtime error message? for example :

      Run-time error '1004'. 
      Application-defined or operation-defined error.

      According to Microsoft help forum, the above message occur when you have cell that contain more than 911 characters in one of your source files.
      More info :

  7. Lal

    What if I have to combine multiple worksheets in multiple workbooks to one sheet,and the number of columns is not uniform in all the worksheets?
    I have 12 workbooks with 60/62 worksheets each. The last column is AI/AJ/AL in different worksheets.

  8. Nagaraju. Gampa


    using this website i have collatted different excel files into to new file.

    finally i got the result.

    Thank you very much for this.
    our article is really a big help for me

    . Thank you. 🙂
    Nagaraju G

  9. swsisack

    M Riza, could you post the full code that dacaprice is asking for, I do not understand where to past those two new lines of code. I too want to combine seperate sheets into a single file, where each file comes in as a new tab at the bottom. Thanks in advance

    • M Riza


      Dim WSA As Object

      Just bellow this line:

      Dim mergeObj As Object, dirObj As Object, filesObj....

      And then insert the following 2 lines

      Set WSA = ThisWorkbook.Worksheets.Add

      just bellow this line:

      For Each everyObj In filesObj

      The code will be:

      Dim mergeObj As Object, dirObj As Object, filesObj...
      Dim WSA As Object
      Application.ScreenUpdating = False
      For Each everyObj In filesObj
      Set WSA = ThisWorkbook.Worksheets.Add
      Set bookList = Workbooks.Open(everyObj)
  10. faiz

    Hi Riza
    Many Thanks for this code.
    Can You please advise how can I add codes for making the rows height = 18 & coulmn width to autoselect.

    • M Riza

      Hi, try add the following code just before the last line (End Sub)

      Range([A1], [A65536].End(xlUp)).EntireRow.RowHeight = 18
  11. Kiara

    Hi M Riza, can you please provide me code for merging sheet 1 of all excel sheets, sheet 2 of all excel sheets and so on in 1 workbook.

  12. Hernan

    Hi Riza, is there any way to identify where each row is coming from. For example creating a code that grabs the source file name and paste it into a new column in the master sheet?

    • M Riza

      Unfortunately every loop on this method use range of cells and not by iterating every cells. Which mean with this method, you can not put any value to cells within selected range. But you still can put value (for example workbook name) into single cells after or before copy or paste process occur.

      Look for the following line

      Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

      and then add the following line

      Range("A65536").End(xlUp).End(xlToRight).Offset(0, 1).Cells.Value = bookList.Name

      This will add a workbook name on the last row after the last column of every copied workbook.

  13. MLin

    I tried this with sheets with few columns and it worked. However, I’m trying it with 22 columns and keep getting this error: Run time Error ‘438 Object doesn’t support this property or method.

    What I am doing that is causing this to happen? Thanks!

    • M Riza

      I’m not really sure what could be the problem. Can you share some of your files so i can try to merge them on my pc?
      Oh and btw, i also made a tiny command line tool to merge spreadsheets files, if you want to try, please read it here

      • MLin

        Hi, I tried your tool. It did not work for me. It may be because I have Excel 2013.

      • M Riza

        Ic, can you provide me more detail info, like error message or screenshots?

  14. Dheeraj

    Hi Riza, its very helpful to me, however can you please suggest how can we use this code were we have to combine data from sheet1 and sheet2 to from each excel file. so the data from each excel file sheet1 combine into the master file sheet1 and data from sheet2 from each excel file will be combine into the sheet2 of the master file.

    • M Riza

      Hi, i bellieve you want to merge all worksheets into related worksheets on master file. In this case please read my newer post in here.

  15. Mittu

    I cannot get this script working on excel 2013.
    It copies the first line from all the sheets.
    Is this tested in excel 2013? Any workaround?

  16. Mittu

    Finally figured it out.
    The script will copy the rows only if the first column is not empty.
    Is this expected?

  17. Mittu

    Is it possible to fix this?

    • M Riza

      Hi, actually i never tried to run the script on Excel other than 2007. But some said that the script worked also on 2013. And regarding the empty first column, i guess it’s because of the “range” function within the script which only use column “A” to find out the last used row.

      Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy


      Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial

      Try change this into “B” or any other first column you have on your workbooks.

  18. Eko Nur SH

    Hello. Thank you for sharing this article. It very helps me. 🙂

    I only have a question. How if in the selected file I have more than 1 sheet and I want to copy all of the sheets? I try this source code and it does not work (other sheets is not copied, only the last sheet in each workbook)

    • M Riza

      Hi, plase read my recent post about it here or you can also use the latest tool i’ve made in here.

  19. Gavin


    I have tried a few of these merge macros and I have had the same issue with all of them. It changes the date from DD/MM/YYYY to MM/DD/YYYY. Is there anyway to stop it doing this, I am merging .CSV files, which I think could be the issue??


    • M Riza

      Hi, have you tried changing your computer region format on control panel?
      According to microsoft office support web If you change your date setting in Control Panel, the default date format in Excel will change as well.

Leave a Reply


Loading Google+ Comments ...
Loading Facebook Comments ...