Do you have several Excel sheets or Excel tables that you would like to join together to create a single file or table? This job may be more complex than you think, especially if there are many sheets to be joined. In this article, however, we will see different methods for merge multiple excel sheets and you will surely find the one best suited to your work needs.
#1. Merge multiple Excel sheets with copy and paste
#2. Merge multiple Excel sheets with Merge Excel
#3. Merge multiple Excel sheets with “Indirect” formula
#4. Merge multiple Excel sheets with VBA macros
Method #1. Copying data
This is perhaps the most obvious and banal method but it is not always the best. It involves selecting the range of source cells, copying them and pasting them into the main worksheet or new excel file we are creating. The disadvantage? Well, this method is very annoying if you have to manage multiple worksheets or multiple cell ranges. But if you only have a little data to manage, this method might be fine.
Method #2. Using the Merge Excel program
If you have many Excel files to merge, or many Excel sheets to combine together, then the solution we propose is to use the program Merge Excel. This program (compatible with Windows computers) is capable of merge multiple excel sheets into a new sheet or merge entire excel files into one complete excel file. This tool also supports importing multiple CSV files, text files, DBF files to output a single MS Excel file. Here are the links where you can download it demo version free of the program:
Once downloaded and installed Merge Excel on your PC, you will see a splash screen like this:
The program will open in Italian, so it will be really simple to use. Here are the main steps to follow for Merge multiple Excel sheets and create a single worksheet
Step 1: Load the Excel files (containing the sheets to be merged) by clicking on the button with the + symbol at the top left. Alternatively you can also drag files from your computer into the program.
Step 2: Select the Excel file and once selected you will be able to decide (from the section at the bottom of the program) the type of combination to make (files, worksheets, files and sheets, rows, columns).
Step 3: Select the Excel sheets (sheets) you want to merge (by double clicking or clicking on the right arrow) and decide whether to merge them into a new sheet or a new Excel file (bottom left)
Step 4: Click on the button above Start Merging at the bottom right to merge Excel sheets
Method #3. Use the “Indirect” formula
This method has the disadvantage of being a little more complicated. This works if you have your files in order and just want to import a few values. With the INDIRECT formula the original files remain as they are and the formula INDIRECT only searches for values within these files. If you delete the files, you will get #REF! error.
With the formula INDIRECT you can link to other files. Necessary condition: The source file must be open in the background. For example, in the image above on the right we have inserted the original file, on the left side instead the image of the Excel file in which the “Indirect” formula is applied with the main workbook. The INDIRECT formula has only one argument: the link to another cell which can also be located in another workbook.
Method #4: Merge Excel Sheets with a VBA Macro
Aren’t you afraid of using a simple VBA macro? So here’s how to do it.
- Go to the Developer menu. If you cannot see this item in the Excel menu, right-click on the menu and click on “Customize menu…”. On the right side set the check mark to “Developer“.
- Click Visual Basic on the left side of the developer toolbar
- Right-click the workbook name and click Insert -> Module
- Copy and paste the following code in the VBA module. Place your cursor inside the code and click start (the green triangle) at the top. That’s all.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | Sub mergeFiles() ‘Merges all files in a folder to a main file.
‘Defines variables: Dim numberOfFilesChosen, the As Integer Dim tempFileDialog As fileDialog Dim mainWorkbook, sourceWorkbook As Workbooks 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
‘Loops attraverso all selected workbooks For the = 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 the
End Sub |
Other VBA macro codes useful for merging multiple Excel sheets into a single sheet are present in this post of the forum ExcelVBA.it
![Microsoft's New Mission Statement, Apple Rewrites History, & More... [Tech News Digest]](https://www.win-keys.com/wp-content/uploads/2024/05/satya-nadella-microsoft-mission-336x220.jpg)
English 
Italian 
Spanish