by The tutorial shows three ways to combine Excel files into one: by copying sheet tabs, running VBA, and using the Copy Worksheets tool.
It is a lot easier to process data in a single file instead of switching between numerous sources. However, merging multiple Excel workbooks into one file could be a cumbersome and long process, especially if the workbooks you need to combine contain multiple worksheets. So, how would you approach the problem? Will you be coping sheets manually or with VBA code? Or, do you use one of the specialized tools to merge Excel files? Below you will find a few good ways to handle this task.
Note. In this article, we are going to look at how to copy sheets from multiple Excel workbooks into one workbook. If you are looking for a quick way to copy data from several worksheets into one sheet, you will find the detailed guidance in another tutorial: How to merge multiple sheets into one.
How to merge two Excel files into one by copying sheets
If you have just a couple of Excel files to merge, you can copy or move sheets from one file to another manually. Hers's how:
- Open the workbooks you wish to combine.
- Select the worksheets in the source workbook that you want to copy to the main workbook.
To select multiple sheets, use one of the following techniques:
- To select adjacent sheets, click on the first sheet tab that you want to copy, press and hold the Shift key, and then click on the last sheet tab. This will select all worksheets in between.
- To select non-adjacent sheets, hold the Ctrl key and click on each sheet tab individually.
- With all worksheets selected, right click on any of the selected tabs, and then click Move or Copy….
- In the Move or Copy dialog box, do the following:
- From the Move selected sheets to book drop-down list, select the target workbook into which you want to merge other files.
- Specify where exactly the copied sheet tabs should be inserted. In our case, we choose the move to end option.
- Select the Create a copy box if you want the original worksheets to remain in the source file.
- Click OK to finish the merge process.
The screenshot below shows the result - sheets from two Excel files combined into one. To merge tabs from other Excel files, repeat the above steps for each workbook individually.
When coping sheets manually, please be aware of the following limitation imposed by Excel: it is not possible to move or copy a group of sheets if any of those sheets contains a table. In this case, you will have to either convert a table to a range or use one of the following methods that do not have this limitation.
How to merge Excel files with VBA
If you have multiple Excel files that have to merged into one file, a faster way would be to automate the process with a VBA macro.
Below you will find the VBA code that copies all sheets from all Excel files that you select into one workbook. This MergeExcelFiles macro is written by Alex, one of our best Excel gurus.
Important note! The macro works with the following caveat - the files to be merged should not be open physically or in memory. In such a case, you will get a run-time error.
How to add this macro to your workbook
If you'd like to insert the macro in your own workbook, perform these usual steps:
- Press Alt + F11 to open the Visual Basic Editor.
- Right-click ThisWorkbook on the left pane and select Insert > Module from the context menu.
- In the window that appears (Code window), paste the above code.
For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Alternatively, you can download the macro in an Excel file, open it alongside your target workbook (enable macro if prompted), then switch to your own workbook and press Alt + F8 to run the macro. If you are new to using macros in Excel, please follow the detailed steps below.
How to use the MergeExcelFiles macro
Open the Excel file where you want to merge sheets from other workbooks and do the following:
- Press Alt + F8 to open the Macro dialog.
- Under Macro name, select MergeExcelFiles and click Run.
- The standard explorer window will open, you select one or more workbooks you want to combine, and click Open. To select multiple files, hold down the Ctrl key while clicking the file names.
Depending on how many files you've selected, allow the macro a few seconds or minutes to process them. After the macro completes, it will notify you how many files have been processed and how many sheets have been merged:
Combine multiple Excel files into one with Ultimate Suite
If you are not very comfortable with VBA and looking for an easier and faster way to merge Excel files, have a look at the Copy Sheets tool, one of 70+ time saving features included with our Ultimate Suite for Excel.
With the Ultimate Suite, merging multiple Excel workbooks into one is as easy as one-two-three (literally, only 3 quick steps). You don't even have to open all of the workbooks you want to combine.
- With the master workbook open, go to the Ablebits Data tab > Merge group, and click Copy Sheets > Selected Sheets to one Workbook.
- In the Copy Worksheets dialog window, select the files (and optionally worksheets) you want to merge and click Next.
Tips:
- To select all sheets in a certain workbook, just put a tick in the box next to the workbook name, all the sheets within that Excel file will be selected automatically.
- To merge sheets from closed workbooks, click the Add files… button and select as many workbooks as you want. This will add the selected files only to the Copy Worksheets window without opening them in Excel.
- To copy only a specific area in a certain workbook, hover over the sheet name with your mouse, then click the Collapse Dialog icon
and select the desired range. By default, all data is copied.
- Select one or more additional options, if needed, and click Copy. The screenshot below shows the default settings: Paste all (formulas and values) and Preserve formatting.
Allow the Copy Worksheets wizard a few seconds for processing and enjoy the result!
To have a closer look at this and other merge tools for Excel, you are welcome to download an evaluation version of Ultimate Suite.
Other ways to merge Excel sheets and combine data
The above examples have demonstrated the best techniques to merge multiple Excel files into one. For more ways to combine sheets in Excel, please check out the following resources.
Available downloads
Macro to merge multiple Excel files (.xlsm file)
Ultimate Suite 14-day fully-functional version (.exe file)
249 comments
Merging multiple worksheets in to one worksheet is perfectly working fine. but with a small requirement, while merging data, how can we put the original file name (from which the data picked from) in the side by column, this is really very useful while migrating data, Can anybody look in to this and kindly advice?
data1 data2 data3 from original file name
cvcdfds sddfsdf dfssg file1
vcxvxv dsfvsds dfssdg file2
klmvlxkvv kmflk kllm;l file3
how can we merge the data like this.
can anybody help in this?
This is great, how would you modify the code so the tab name is changed to be the source file name?
Hi Sir
We have Created Excel in one consolidate sheet and all party sheet in same excel. I need Link all the sheets link in consolidate what can ido
Hello, I need your help in something...
I have been working on an excel file for sometime, then I asked a friend to help me with a VBA code that would open several hyperlinks (word documents) that i selected, copy them and past them for me in one single word document. He couldn't do it but he asked someone for help... but what that other persone did is he made a new copy of my work and deleted all the sheets and worked on the sheet that i needed this fucntion... now it is hard for me to combine these 2 excels, or to copy that guys VBA code to my original excel file...
in the new VBA code (function) he has a new command/button that does everything...
thank you so much for your help
So i found the solution for my problem, thx anyways
VBA Macro for merging worked great! Thank you!
Hi,
How do I merge multiple sheets into one sheet using column name as the column order (example A, AN , B) does not match for all the sheets? Thank you for your kind help.
Warmest Regards.
Hello Victor!
There are several ways to merge multiple tables or sheets. You can find out more about them on our blog following this link.
However, there is a ready-made solution for your task in Ultimate Suite for Excel. Please have a closer look at the Merge Two Tables and Combine Sheets tools.
You can install Ultimate Suite in a trial mode and test the tools for 30 days for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
I hope this information will be helpful for you.
Worked a treat
Saved a lot of monkeying about
Thx
I can use the script but I need the file name as the name of the imported sheet and not the sheetname.
with regards,
Patrick
Great code. worked beautifully for me. As someone mentioned, a description of what the functions do would be very helpful.
All in all thanks for the effort
In my case, I had to combine csv files. I added .csv extension in the file filer and that pulls in CSV files as well
FileFilter:="Microsoft Excel Workbooks And text Files (*.xls;*.xlsx;*.xlsm;*.csv)
Your macro ran great.
After it runs and pulls all my workbooks together, I have a lot of empty tabs in my master workbook.
Do you have any ideas on what would be causing this?
Hi, is it possible to add each sheet name into the consolidated Sheet?
Hello Bindu,
The current version of Combine Sheets has no option to insert the tables’ names in the resulting table. Our developers will check out this suggestion and try to implement it in one of the future versions, but I cannot give you the exact timing yet.
However, there is a workaround I may recommend you. Add an additional column to each of the tables you are to combine (let’s call it Sheet_Name, for example). Note! This column should be named the same in each sheet.
Then enter the following formula in this column to get the sheet’s name there:
=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1,255)
This column will be added to the resulting table too and you’ll define the original data location by that.
Trying to run this macro on Excel 2013 and get error message "Run-time error '1004': Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook...."
Is there a solution to this? None of the source files are open.
thank you so much....
Hi,
i am using below macro but i need to copy only first sheet. please confirm
----------------------------------------------------------------------------------------------
Sub MergeExcelFiles()
Dim fnameList, fnameCurFile As Variant
Dim countFiles, countSheets As Integer
Dim wksCurSheet As Worksheet
Dim wbkCurBook, wbkSrcBook As Workbook
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
If (vbBoolean VarType(fnameList)) Then
If (UBound(fnameList) > 0) Then
countFiles = 0
countSheets = 0
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wbkCurBook = ActiveWorkbook
For Each fnameCurFile In fnameList
countFiles = countFiles + 1
Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
For Each wksCurSheet In wbkSrcBook.Sheets
countSheets = countSheets + 1
wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
Next
wbkSrcBook.Close SaveChanges:=False
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Processed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
End If
Else
MsgBox "No files selected", Title:="Merge Excel files"
End If
End Sub
Hi, Is it possible to combine data from two workbooks only when,
In 1st workbook, I have Sheet1 & Sheet 2 data,
&
in 2nd workbook, I have same sheet1 and sheet 2 data,
Required result: When I combine 1 & 2 worksheets, A data should get an update in A sheet and B data should get an update in the B sheet itself.
I'm now the hero of my office thanks to your code... thank you!!
I have 30 excels date wise data and I want to combine it into single excel. Please help.
Hi all,
i need vba to merge multiple sheets data in one excel with same sheets [data should be merged accordingly with same sheets]
Please assist
Thank you
Hi!
This article was really helpful. But I am trying to do the exact same function for .xlsx in Libre Office in an Ubuntu environment, I am writing a python script using pandas and numpy.
Is there any easier way with macros in Libre Office.
Any help would be appreciated.
Thank you
Dear author, I want to combine specific excel sheets from multiple excel files. I want to do it with VBA as there are 100 + excel files. Please help me out.