How to Connecting and Merging Multiple Excel Files into 1 File Easily

Learning how to connecting and merging multiple excel files into 1 file easily doesn't have to be complicated. This updated guide presents a clear workflow, practical checks, and troubleshooting advice you can use while completing the process.

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 open 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.

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

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

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

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. You can refer to the guide to join Word files into 1 if you want to do the same with Word files.

Final Thoughts

The most reliable way to handle connecting and merging multiple excel files into 1 file easily is to follow the process in order, verify each important setting, and test the result before moving on. Use the guidance above as a practical reference, then adjust the details for your device, software version, or specific goal.