Instructions for connecting and merging multiple Excel files into 1 file easily

Merge multiple Excel files into one file to easily process data. Let's learn with TipsMake how to use VBA or Move/Copy to do it quickly and accurately.

Why do we need to merge multiple Excel files into 1?

- Convenient in synthesizing data from multiple sources.
- Reduce errors when processing data manually.
- Easy to analyze and report on a single table.

How to merge multiple Excel files into 1 file

* Steps to prepare:

- Computer installed with Excel 2013 or Excel 2016 which is commonly used today.
- Available Excel files for execution.

Method 1: Using VBA code

Suppose you have 2 Excel files with the following data:

File 1: File01.xlsx

File 2: File02.xlsx

To merge data from the above 2 files into a single data file named Book1 (records are on the same Sheet), do the following:

Step 1: Create a new Excel file named Book1 -> then press Alt + F11 -> a window appears -> select the Insert tab -> select Module .

Step 2: Copy the code below into Module 1 .

Sub GopFileExcel()
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 and name it GopFileExcel . Because the Module contains Macro, when you save , there will be a notification -> click OK .

Step 4: Create another Module -> continue to click Insert -> Module .

Step 5: Then copy the entire code below into Module 2 -> save and name it GopSheetExcel .

Sub GopSheetExcel()
Dim Sh As Worksheet
Application.ScreenUpdating = False
[A6].CurrentRegion.Offset(1, 1).ClearContents
For Each Sh In Worksheets
If Sh.Name <> "Gop_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: Go back to the Excel file you just created -> then go to the View tab -> select View Marco (or press the shortcut Alt + F8 ).

The macro dialog box appears -> you select Module 1 (name GopFileExcel ) -> select Run .

The Browse dialog box appears -> you select the path to the data files to merge (here you select the files File01.xlsx and File02.xlsx ) -> select Open .

After you select Open , the data from the two corresponding files will be written to sheet 2 and sheet 3 in the Book1 file.

Step 7: Go to View tab -> View Macro -> select GopSheetExcel -> then select Run .

You will see all the data in 2 Sheets merged into Sheet 1. If you see the order number is incorrect or not standard, re-enter it, and align the cells and columns to make it look nice.

Some notes when merging files using VBA code

- Sheet 2 is merged, sheet 3 is sheet 1.
- If you merge multiple files, the larger the files, the slower the processing speed will be.
- During the process of merging data files, you should not use any additional data functions to avoid causing errors. It is recommended to use simple input data.

Method 2: Use Move/Copy sheets

With this method, you operate similarly to the way to copy and move Sheets in Excel .

Above is a guide to help you join and merge multiple Excel files into a single file. Depending on the amount of data and work requirements, choose the most suitable method. If you want to do the same with Word files, you can refer to the guide  to join Word files into 1 .