You have several Excel workbooks and you want to merge them into one file? This could be a troublesome and long process. But there are 6 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
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
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 your files are 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.
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 have 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.
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 SubMethod 5: Automatically merge workbooks
The fifth way is probably most convenient:
- Press the Merge Files button on the “Home” ribbon in Excel.
- Now select all the files and worksheets you want to merge and the target workbook.
This procedure works well also for many files at the same time and is self-explanatory.
To do that you need a third party add-in, for example our “Magic Merge Manager” (click here to start the download). Besides simply merging all worksheets, you can further finetune the selection.
(New) Method 6: Use the Get & Transform tools
The current version of Excel 365 offers the “Get & Transform” tools to import data. These functions are very powerful and are supposed to replace the old “Text Import Wizard”. However, they have one useful feature: Import a complete folder of documents.
The requirements: The workbooks and worksheets you want to import have to be in the same format.
Please follow these steps for importing a complete folder of Excel files.
- Create a folder with all the documents you want to import.
- Usually it’s the fastest to just copy the folder path directly from the Windows Explorer. You still have the change to later-on select the folder, though.
- Within Excel, go to the Data ribbon and click on “Get Data”, “From File” and then on “From Folder”.
- Paste the previously copied path or select it via the “Browse” function. Continue with “OK”.
- If all files are shown in the following window, either click on “Combine” (and then on “Combine & Load To”) or on “Edit”. If you click on “Edit”, you can still filter the list and only import a selection of the files in the list. Recommendation: Put only the necessary files into your import folder from the beginning so that you don’t have to navigate through the complex “Edit” process.
- Next, Excel shows an example of the data based on the first file. If everything seems fine, click on OK. If your files have several sheets, just select the one you want to import, in this example “Sheet1”. Click on “OK”.
- That’s it, Excel now imports the data and inserts a new column containing the file name.
For more information about the Get & Transform tools please refer to this article.
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
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?
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
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…
“Method 5: Automatically merge workbooks
The third way is probably most convenient:”
Is that a “Monty Python & the Holy Grail” reference? 😀
Haven’t seen it actually. But I corrected the mistake… it’s the fifth way 😉
Method 4 has made my life very easy……………………..
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
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)
Hi Hari,
I’m working on an advanced merge function for my add-in. But it’ll take some time.
Best regards,
Henrik
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.
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
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 .
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?
Hi Jo,
Probably it’ll work as well. Just give it a try…
Henrik
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.
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.
I have multiple workbooks with multiple worksheets (same columns for all workbooks, but different columns within each workbook ie. both book1 and book2 have sheet1 and sheet2, or more). Your code adds ALL sheets one after another, so I end up having 4 tabs in the output. Can you tweak it so sheet1 from book1 and book2 are merged in one new sheet, and sheet2 from book1 and book2 are merged in another sheet (2 sheets 1 workbook in the output)? Thanks!
HI Henrik,
thank you very much for the VBA code.
Could you please help me a bit related to that code?
I should copy only the 2nd, 3rd and 4th sheets from each excel workbook into a separate one. How should I change the code to be able to avoid the remaining tabs?
Thank you,
Renato
I seriously love you for posting #4 (with the adjustment in your comment reply. Instead of requesting more customization I’ll tell you a story. I’ve been on a self-directed crash-course in VBA since stepping into an analyst role two weeks ago, tasked with (among other things) collecting and tracking responses for procedural non-compliance from a few hundred field units. Old method: field offices were sent a 32-column data export and attempted to copy/paste the rows which apply to their office into a separate “Response Form” (aka a Word doc) along with additional identifying info and investigation/action details. The analyst tracked responses by highlighting lines on a pivot table, which was challenging because few field units could successfully copy/paste 32 cells or provide all requested info or type a Subject line that distinguished one response from 100 others. My method: Turned the Word Doc into a 32-column worksheet, merged it with the data export, added a Double-Click event function so *anyone* can fully populate their response form directly from the data export tab; gave the form a 1-click “Submit” function which references identifying data (via their double-click event) to rename the worksheet which then duplicates and emails itself to me with a subject line that also references identifying data; found Outlook VBA that extracts all attachments from the folder which their responses (with those uniform and predictable subject lines) are filtered to, to an Import-folder on my desktop; and a tracker column on my master data export which matches identifying info from the response sheets as I import them into the tracking worksheet. The ONLY thing missing has been a convenient way to batch-import those attachments into the tracker without requiring an Add-In (miles of red tape to get one approved). I knew it could be done but was unable to find an answer for the past two weeks before stumbling onto this post. You’re a God-send at the end of a 13-hour day. Thank you.
Number 6 works brilliantly! Thanks!
Hi
I try to merge 300 excel files. Although I use the method 5, when I combine sheets I have got some problem. the problem is I have got billion cell at end of my first sheet’s number … what I can do with this cells.
The problem gone when I save the excel sheets one more time , but I dont want to ( save as) all my 300 excel files again.
Excellent !!!
Is it possible to name the sheets accordingly to their filenames?
every ten minutes i’ll recieve and excel file how can i merge this file which is been recived every ten minutes to a new excel file automatically ?
I have 70 workbooks with multiple sheets, I only want a specific sheet from each workbook, how can I tweek the code in #4 to accomplish this?
I used method 6, and it worked amazingly. That saved me about 3 hours of manually copy and pasting 6 months of daily files into a single sheet. Thank you so much for the instructions, very VERY much appreciated.
Yes, I’m also a big fan of PowerQuery! 🙂
Method 4 worked! Thanks!
Glad it worked 🙂
Hi Henrik,
On Method 4, how do I add a step to include a column that adds the file name to each line item?
I try to use #4 VBA code, however I get a Run-Time error ‘424’ Object required. Strange when it works for every one else. Do you have any explanations for this?
I try to use #4 VBA code, however I get a Run-Time error ‘424’ Object required. Strange when it works for every one else. Do you have any explanations for this?
This works great (method 4). This is way better than other vba online. Thanks.
However, I’m looking to merge just a single specific worksheet of many workbooks into one. Is this something we can look forward to?
Can we go further and get a specific cell/s, of a specific sheet, of several selected workbooks?
Should be something like:
Instead of
‘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
try this:
‘Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
If tempWorkSheet.Name = “worksheet name to copy” then
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
End If
Next tempWorkSheet
I haven’t tried it, but please let me know if it works.
Is there a way to merge workbooks, but not copy and paste. I’ve got myself in a bit of a pickle. I really need to combine a couple workbooks, but they are each fairly complex with various tables, pivots, named ranges and functions, and a few macros sprinkled around. I guess I’ll have to do this by hand. The data will be easy to migrate, but I’ll have to basically rebuild all the functionality of the old in a new combined workbook.
Method 4 is fantastic!!! thank you.
Dear Sir,
I was able to follow method 5, and successfully able to implement it except at one point.
I have a column in the workbooks having data such as 0.5, 1, 2.5
However, after consolidation with method 5, it makes 0.5 as 0, 2.5 as 2.
Thus, it will be very helpful if you can share solution to the problem.
Regards
Taruchit
Is there a routine to pick up 4 files and merge into 1 file with 4 tabs ?
Need to do this for 4000 files without manual intervention. 4000 files to be converted into 1000 files with 4 tabs each.