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)
66 comments
Hi! This is very useful. God bless you! I have trouble making the command line: "copy *.xls merged.xls" or "concat *.xls merged.xls" work in Command Prompt or Terminal and I am very grateful to have found this as it is the easiest code in the internet - I don't need to change the code just to input the directory of the files to be merged or the input the desired filename, so for that, thank you! I just shared this with my co-workers who have never used VBA before.
By any chance, can you write up a code which will merge multiple workbooks into one worksheet instead of one workbook with multiple sheets? I know how to use Power Query but sometimes it leaves out some sheets after the merging and it usually causes Excel to stop working as I am merging several files with average 6,000 to 10,000 rows.
Also, do you know what other command line I could use on Command Prompt or Terminal other than the "copy" or "concat"?? Would really appreciate it.
Hi! We do not do VBA code creation or customization on request. However, you can try to solve your problem using the Combine Sheets tool. Merge data from multiple worksheets into a single Excel sheet based on column headings. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hello,
I am getting the following error every time I run this macro:
when I run it I get the following error:
Run-time error '1004':
Method 'GetOpenFileName' of object '_Application' failed
Anyone else gotten this or know how to troubleshoot? thanks
Hello Arman!
Unfortunately, I can't see how you inserted the VBA code into your file and how you select the workbooks to merge them. Try using sample file linked at the end of this article.
Hi Alexander,
I'm facing the same issue as Arman. I get this error immediately when clicking on 'Run' i.e. I don't even get the chance to choose the workbooks.
Run-time error '1004':
Method 'GetOpenFileName' of object '_Application' failed
When I click on debug, this seems to be the line that is causing the issue (it is highlighted in yellow)
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
Do you know how I can resolve this? Thank you!
Hello Hayley!
Try using the sample file, the link to which is at the end of the article.
Hi Alexander,
Not sure if this is relevant but I'm using a Mac - could this be the reason why it isn't working?
Warm regards,
Hayley
Hi! That is the main reason. VBA does not work on macOS.
kindly sir make a vba code for unique values means i have many excel file and every sheet have many sheets i want to copy all same itoms all the sheets and paste new sheet which i name master sheet but dublecate itom note copy in the new workbook and also generate a vba code to link master sheet to all sheet if i put the value in master sheet this value autimatic paste all the sheets which i have this itom
Hi! We do not do VBA code creation or customization on request.
You can find and copy unique values using the Duplicate Remover tool. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hi,
I would like to put multiple individually saved Excel sheets into one workbook. I would also like for the tabs to have the names of the excel sheets once the process is done.
Thank You
Hi! We have a tool that can solve your task in a couple of clicks. I recommend paying attention to the Merge Sheets tool. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hello! This is so helpful, thank you so much!
This gets me halfway to where I want to get. I often have to go one step further and combine all tabs into one sheet, adding each tabs data to the bottom of the one ahead of it.
Are you aware of any macros or tools that can do this?
Hello! I recommend paying attention to the Combine Excel sheets tool. You can use it to combine data from multiple worksheets into a single Excel worksheet based on column headings. You can also add a blank row after each subset of data. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hi, I did use the macro above and it works just fine. However, it brings ALL the sheet from the workbooks across to the new workbook. How can I adjust the code to only bring One of the sheet from all the workbook? The sheet is called ABC in all of the workbooks.
Thank you
Hi! Copy this ABC sheet into a new workbook. Then copy this sheet from this new workbook to all other workbooks.
Thanks Alexander. but let me rephrase my question.
I need to copy one worksheet from multiple workbooks into multiple worksheets in one workbook. but I don't want ALL of the sheets within all of the workbooks to be copied across. Only a selected sheet.
I also need to bring in the filename across, perhaps as a new column in the worksheet.
would it be possible to adjust the VBA above to only pick one sheet name and also add the filename as a column?
Many thanks
Hi! If I understand your task correctly, our Copy Sheets tool may help you solve it in a few clicks. Use option "Copy data from the selected sheets with the same name to one sheet". It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
VBA script combines data in different sheets but I need to combine multiple workbook with single sheet data into single sheet. I wanted try to combine all sheets into through Data Query, do we have convert all the data sheets into tables. Do we have any short cut for converting multple data sheets into tables.
I recommend paying attention to the Combine Sheets tool and Copy Sheets tool. These tools are included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.
Hello,
Is there any way to get data from specific cells from multiple spreadsheets and consolidate them in one spreadsheet?
This is the situation: I want to consolidate 150 spreadsheets into one and I want to get the data in cell B4 and Cell J10 of every spreadsheet.
Thank you
Hello!
If I understand your task correctly, you can consolidate data from selected cells of multiple worksheets using these guidelines: Consolidate data in Excel and merge multiple sheets into one worksheet. If this is not what you wanted, explain in detail what result you want to get.
For example if there's sales data for Fruits like Mango, Apple, Banana, Kiwi in four worksheets in a workbook named Sales_March and there are similar workbooks for rest of the months with same 4 worksheets. Now how do i combine the yearly sales data for Mango in one sheet, Apple in one sheet etc for all the four fruits in a new workbook? I have been trying to figure out a direct method for this but couldn't find anything yet.
Hello!
We have a ready-made solution for your task. I recommend paying attention to the tool Combine Sheets. You can merge data from multiple sheets and select columns to merge. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
How is it possible to merge two xcel spreadsheets keeping the columns that are in target and removing any rows from target that are no longer present in source.
A b c d. ->. A b c.
1. 1
2. 3.
3.
Result
A b c d
1
3
Hello!
To merge tables, try the Merge Two Tables tool. It allows you to select columns to add. It is possible not to add mismatched rows. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
I am trying to combine multiple excel sheets, however, I only need one tab from each sheet. The tab name is the same in all the sheets I am trying to combine. Is there a way to isolate and just pull the one tab from each excel sheet?
Hello!
I can recommend you a special tool for сopying selected worksheets from several workbooks to one file - Copy Sheets. You can combine all the sheets you need from different Excel files into one new workbook in a couple of clicks. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
I am trying to merge spreadsheet B that has phone numbers, (total 175), on them, and match them to another spreadsheet A , that has 450 rows.
SS B was returned to me by a company that provides phone numbers, but they could only provide 175 numbers out of the original 450 rows of data.
Because I have more notes on Spreadsheet A, I would like to merge Spreadsheet B to SS A.
How can I do this ??
Hello!
You can find the examples and detailed instructions here: How to merge Excel sheets into one.
Can this macro be modified to include CSV files?
Hi,
Adding *.csv; to the list in the line below allows you to select and merge .csv files.
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
Which becomes
fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.csv;*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
:)
Hello!
You can import the CSV file into Excel and work with this data. If necessary, export the Excel data to a CSV file back.
Hellow, how can I use macros if I want for example I have thirty people and they make transactions every week so if I click among of one person out of thirty people in Excell I want to bring me all his or her information concerning transactions that they made, so it is possible?
Hello!
I think you can use the FILTER function. You can find the examples and detailed instructions here: Excel FILTER function - dynamic filtering with formulas. I hope my advice will help you solve your task.
Would it be possible to only merge specific workbooks in a folder? I have a folder with multiple files and need to pair them up. So instead of creating multiple folders, I was thinking of a table where Workbook A would pair up with Workbook B, C with D? can this be achieved?
Hello!
If I understand your task correctly, our Copy Sheets tool may help you solve it in a few clicks. It allows you to merge multiple Excel sheets into one. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
How can I use this macro if I want to copy over just 1 of the 4 sheets in each workbook? I'm having trouble successfully referencing the single sheet. Also, how can we alter this to paste values?
Hello!
We have a ready-made solution for your task. The COPY SHEETS tool can copy the sheets of your choice to the desired workbook.
We created a template with header and column names, we decided to split the task across workers, gave each of them the designed template with same column names. Each returned excel files with partly filled data (this I due to division of labour on our on end, reason why we created the template) since these files have the same row names and volume, we would like to merge the various files into one and have excel merge this files.
Take for instance worker A works on the first 20 and B works on 21-40 or in no definite pattern, would excel be able to merge these files together? Please I need a solution, thanks
Hello!
If I understand your task correctly, our Copy Sheets tool may help you solve it in a few clicks.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
If something is still unclear, please feel free to ask.
Is there a way to print this without trying to copy as it? I need to do this but know I won't remember all this. Brain works better at 7am. And which is the easiest method? Thank you.
Hi!
If I understood the problem correctly, in order to print this article, you can use the key combination Ctrl + P
Thanks for the script.
This successfully merges the selected files in a single folder location. However, is there a way if the files to be merged are placed at different folder locations.
Hi!
If you are using Ultimate Suite then files can be written to any folder.
hello. is it possible to merge multiple excel files into 1 excel file but with multiple sheet?
Hello!
If I understand your task correctly, our Ablebits Data - Copy Sheets - Selected Sheets to One Workbook may help you solve it in a few clicks. It is available as a part of our UltimaIt is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.te Suite for Excel that you can install in a trial mode and check how it works for free.