Buzz
Surely, you're familiar with Microsoft Excel, the spreadsheet program in Microsoft's Office suite, designed to help you record, organize, and analyze data in tabular form. This article will guide you on merging Excel files, combining multiple Excel files into one.
How to Merge Multiple Excel Files into One
* Preparation steps:
- Computer with Excel 2013 or Excel 2016 commonly used today.
- Have the Excel files ready to merge into one file.
You can merge multiple Excel files into one file using either of the two methods we introduce below.
Method 1: Using VBA code to merge files
Let's say you have 2 Excel files with the following data:
File 1: Mytour-File01.xlsx
File 2: Mytour-File02.xlsx
To merge data from the two files into a single data file named Book1 (records on the same sheet), follow these steps:
Step 1: Create a new Excel file named Book1 -> then press the shortcut Alt + F11 -> a window appears -> select the Insert tab -> choose Module.
Step 2:
Sub MergeExcelFiles()
Dim FilesToOpen
Dim x As Integer
On Error GoTo ErrHandler
Application.ScreenUpdating = False
FilesToOpen = Application.GetOpenFilename _
(FileFilter:='Microsoft Excel Files (*.xlsx), *.xlsx', MultiSelect:=True, Title:='Files to Merge')
If TypeName(FilesToOpen) = 'Boolean' Then
MsgBox 'No Files were selected'
GoTo ExitHandler
End If
x = 1
While x <=>
Workbooks.Open Filename:=FilesToOpen(x)
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Step 3: Save Module 1 as MergeExcelFiles. Since the Module contains a Macro, you will receive a prompt when saving -> click OK.
Step 4: Create an additional Module -> continue by clicking Insert -> Module.
Step 5: Then paste the entire code snippet below into Module 2 -> save it and name it MergeSheetExcel.
Sub MergeSheetExcel()
Dim Sh As Worksheet
Application.ScreenUpdating = False
[A6].CurrentRegion.Offset(1, 1).ClearContents
For Each Sh In Worksheets
If Sh.Name <> 'Merge_File' Then
With [B65500].End(xlUp).Offset(1)
[A6].CurrentRegion.Offset(1, 1).Copy Destination:=.Offset(0)
End With
End If
Next Sh
Application.ScreenUpdating = True
Columns('E:E').Hidden = False: Randomize
[A5].Resize(, 6).Interior.ColorIndex = 34 + 9 * Rnd() \ 1
End Sub
Step 6: Return to the Excel file you just created -> then go to the View tab -> select View Macro (or press the shortcut Alt + F8).
A macro dialog appears -> select Module 1 (named MergeExcelFiles) -> click Run.
A Browse dialog appears -> navigate to the paths of the data files to merge (here you select the files Mytour-File01.xlsx and Mytour-File02.xlsx) -> click Open.
After you click Open, the data from the corresponding two files will be written into Sheet 2 and Sheet 3 in the Book1 file.
Step 7: Go to the View tab -> View Macro -> select MergeSheetExcel -> then click Run.
You will see all data from Sheets 2 and 3 merged into Sheet 1. If you find incorrect or irregular numbering, re-enter them and adjust the cells and columns to make it visually appealing.
Some notes when merging files using VBA code
- Sheet merging sheet2, sheet 3 is sheet 1.
- If you merge multiple files, the larger the files, the slower the processing speed.
- During the file merging process, it's advisable not to use any additional data functions to avoid errors. It's recommended to use pure data input.
Method 2: Merge multiple files into one file using Move or Copy
With this file merging method, the procedure you perform will be similar to copying or moving sheets in Excel.
To understand how to do it, refer to the detailed instructions on copying, moving sheets in Excel that we have previously provided.
Above are the ways to merge multiple Excel files into one file that we want to introduce and guide you. With these 2 methods in this article, choose the most suitable one according to your actual work needs.
Similarly, with Word, you can also merge Word files into one, combining multiple Word files into one as Mytour has shared. Wish you success!
