Merge Excel Files: 5 Simple Ways of Combining Excel Workbooks

merge, excel, workbooks, files, join, put together

Merge Excel Files: How to Combine Workbooks into One File

posted on 22 Jul 16

comments 16

You got several Excel workbooks and you want to merge them into one file? This could be a troublesome and long process. But there are 5 different methods of how to merge existing workbooks and worksheets into one file. Depending on the size and number of workbooks, at least one of these methods should be helpful for you. Let’s take a look at them.

Contents

Method 1: Copy the cell ranges
Method 2: Manually copy worksheets
Method 3: Use the INDIRECT formula
Method 4: Merge files with a simple VBA macro
Method 5: Automatically merge workbooks

Method 1: Copy the cell ranges

Copy and paste the source content into your main workbook.

The obvious method: Select the source cell range, copy and paste them into your main workbook. The disadvantage: This method is very troublesome if you have to deal with several worksheets or cell ranges. On the other hand: For just a few ranges it’s probably the fastest way.

Method 2: Manually copy worksheets

Copy worksheets separately to the “master” workbook.

The next method is to copy or move one or several Excel sheets manually to another file. Therefore, open both Excel workbooks: The file containing the worksheets which you want to merge (the source workbook) and the new one, which should comprise all the worksheets from the separate files.

Select the worksheets in your source workbooks which you want to copy. If there are several sheets within one file, hold the Ctrl key and click on each sheet tab. Alternatively, go to the first worksheet you want to copy, hold the Shift key and click on the last worksheet. That way, all worksheets in between will be selected as well.
Once all worksheets are selected, right click on any of the selected worksheets.
Click on “Move or Copy”.
Select the target workbook.
Set the tick at “Create a copy”. That way, the original worksheets remain in the original workbook and a copy will be created.
Confirm with OK.

Method 3: Use the INDIRECT formula

The next method comes with some disadvantages and is a little bit more complicated. It works, if you got your files in a systematic file order and just want to import some certain values. You build your file and cell reference with the INDIRECT formula. That way, the original files remain and the INDIRECT formula only looks up the values within these files. If you delete the files, you’ll receive #REF! errors.

With the INDIRECT formula you can link to other files. Only condition: the source file must be open in the background. On the right hand side: The original file. On the left hand side: The main/ combined workbook.

Let’s take a closer look at how to build the formula. The INDIRECT formula has only one argument: The link to another cell which can also be located within another workbook.

Copy the first source cell.
Paste it into your main file using paste special (Ctrl + Alt + v ). Instead of pasting it normally, click on “Link” in the bottom left corner of the Paste Special window. That way, you extract the complete path. In our case, we got the following link:
=[160615_Examples.xlsm]Thousands!$C$4
Now we wrap the INDIRECT formula around this path. Furthermore, we separate it into file name, sheet name and cell reference. That way, we can later on just change one of these references, for instance for different versions of the same file. The complete formula looks like this (please also see the image above):
=INDIRECT("'"&$A3&$B3&"'!"&D$2&$C3)

Important – please note: This function only works if the source workbooks are open.

Excel too slow? Speed it up. Get the book now!

Merge Excel Files: How to Combine Workbooks into One File

Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!

Learn how Microsoft Excel performs calculations
Use the simple and effective step-by-step guide to master each method
Get to know the impact each method will have on performance

Learn more or get it on Amazon!

Method 4: Merge files with a simple VBA macro

You are not afraid of using a simple VBA macro? Then let’s insert a new VBA module:

Go to the Developer ribbon. If you can’t see the Developer ribbon, right click on any ribbon and then click on “Customize the Ribbon…”. On the right hand side, set the tick at “Developer”.
Click on Visual Basic on the left side of the Developer ribbon.
Right click on your workbook name and click on Insert –> Module.
Copy and paste the following code into the new VBA module. Position the cursor within the code and click start (the green triangle) on the top. That’s it!

Sub mergeFiles()
    'Merges all files in a folder to a main file.
    
    'Define variables:
    Dim numberOfFilesChosen, i As Integer
    Dim tempFileDialog As fileDialog
    Dim mainWorkbook, sourceWorkbook As Workbook
    Dim tempWorkSheet As Worksheet
    
    Set mainWorkbook = Application.ActiveWorkbook
    Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker)
    
    'Allow the user to select multiple workbooks
    tempFileDialog.AllowMultiSelect = True
    
    numberOfFilesChosen = tempFileDialog.Show
    
    'Loop through all selected workbooks
    For i = 1 To tempFileDialog.SelectedItems.Count
        
        'Open each workbook
        Workbooks.Open tempFileDialog.SelectedItems(i)
        
        Set sourceWorkbook = ActiveWorkbook
        
        'Copy each worksheet to the end of the main workbook
        For Each tempWorkSheet In sourceWorkbook.Worksheets
            tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
        Next tempWorkSheet
        
        'Close the source workbook
        sourceWorkbook.Close
    Next i
    
End Sub

Sub mergeFiles()

'Merges all files in a folder to a main file.

'Define variables:

Dim numberOfFilesChosen, i As Integer

Dim tempFileDialog As fileDialog

Dim mainWorkbook, sourceWorkbook As Workbook

Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook

Set tempFileDialog = Application.fileDialog(msoFileDialogFilePicker)

'Allow the user to select multiple workbooks

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

'Loop through all selected workbooks

For i = 1 To tempFileDialog.SelectedItems.Count

'Open each workbook

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'Copy each worksheet to the end of the main workbook

For Each tempWorkSheet In sourceWorkbook.Worksheets

tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)

Next tempWorkSheet

'Close the source workbook

sourceWorkbook.Close

Next i

End Sub

Method 5: Automatically merge workbooks

merge, files, professor, excel, tools, join, worksheets

Easily merge Excel files with Professor Excel Tools

The fifth way is probably most convenient:

Press the Merge Files button within the ‘Professor Excel’ ribbon.
Now select all the files you want to merge and all worksheets of these Excel files will be copied to the current workbook.

This procedure works well also for many files at the same time and is self-explanatory.

But you need a third party add-in, for example our “Professor Excel Tools“. You can download and use it for 7 days for free.

This function is included in our Excel Add-In 'Professor Excel Tools'

Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.

Henrik Schiffner

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Comments 16

Daniel Wise

January 16, 2017 • Reply

Hi

I want a routine to compare and merge some workbook into one or only merge some workbooks into one. I don’t want a only copy a sheet, no…. I want to MERGE copies of one workbook into one.

I hope you can help me

Thanks
Sharyn

March 15, 2017 • Reply

Hi. I used method 4 to merge numerous excel files into one workbook.

After each file it gives me a message that says “The name ‘NvsInstanceHook’ already exists. Click Yes to use that version of the name or click No to rename the version of ‘NvsInstanceHook’ you’re moving or copying”
I click yes. Then it asks me if I want to save or not save the file. I click not save,

How do I get the script to automate answering both those prompts so I don’t need to manually select?

Also, how do I save this script so I can keep using it over and over again?
Jacqui

October 20, 2017 • Reply

Hi

I used the VBA method which worked like a charm except I had to click “save” after every file was processed. Is there any way to include that in the module?

Thanks!

Jacqui
- Henrik Schiffner
  
  November 9, 2017 • Reply
  
  Hi Jacqui,
  Please replace this line:
  
  sourceWorkbook.Close
  
  by these lines:
  
  Application.DisplayAlerts = False
  sourceWorkbook.Close
  Application.DisplayAlerts = True
  
  This should avoid the error messages. But please make sure that your work is saved…
Brent R.

November 8, 2017 • Reply

“Method 5: Automatically merge workbooks
The third way is probably most convenient:”

Is that a “Monty Python & the Holy Grail” reference? 😀
- Henrik Schiffner
  
  November 9, 2017 • Reply
  
  Haven’t seen it actually. But I corrected the mistake… it’s the fifth way 😉
Andrew N

November 28, 2017 • Reply

Method 4 has made my life very easy……………………..
Jonas Tiger

January 9, 2018 • Reply

Hi
I tried method 4 (VBA) and it works fine for a simple merge.
But what I want is to update the merge task withou duplicate sheets.
Explaining:
I have three large files updating in a daily basis (each one with one sheet). Then i need to merge all of them into one evaryday.
Using your method 4 for second time, it duplicates sheets, instead of replacing the existing sheets.

So, it would be nice if you provide the changes needed in your code to do the update and, if possible, to execute automatically every 24 hours.

Thank You very much for your time
JT
hari

April 21, 2018 • Reply

i want to combine a specific sheet from the source files to a work book. Not all the work sheets in the source files.Suppose three product files , (product1 , product2 and product3) all the files has many sheets like sales,employees, expenses and so on. But i need only sales sheets from all the 3 product files into one work book (not in one work sheet)
- Henrik Schiffner
  
  May 1, 2018 • Reply
  
  Hi Hari,
  I’m working on an advanced merge function for my add-in. But it’ll take some time.
  Best regards,
  Henrik
vvs

May 10, 2018 • Reply

Hi
when i used vba code it worked fine but i want all the data to merged in one single worksheet instead of seperate worksheets in one workbook.
- Henrik Schiffner
  
  May 10, 2018 • Reply
  
  Hi VVS,
  Please check out this article: https://professor-excel.com/merge-sheets/. After merging the data into one workbook (on separate sheets), you can copy them underneath each other on one single worksheet.
  Best regards,
  Henrik
vvs

May 15, 2018 • Reply

Hi Henrik,
Instead of running the code twice is there any chance that merging the data from different works books into one single workbook on one tym running the vba code .
Jo

May 16, 2018 • Reply

What if you aren’t merging workbooks (.xlsx), but other data files saves as .csv, or .txt, or .dat .. can the VBA script handle those?
sherazad

June 1, 2018 • Reply

Hi,

Thanks very much for your code, I tried it, it works but I have this error message: Run-time error ‘1004’ Method ‘Copy’ of object ‘Worksheet’ failed, could you please help me on this.

Best regards.
vvs

June 25, 2018 • Reply

Hi Henrik, the code corked fine. now ,i want to delete the header rows in the merge file when it is executed and should display only final header row at the top but not all the header rows.