Instructions to merge multiple Excel files into 1 File

The following article details how to combine multiple Excel files into a single file.

For example, there are 2 data files as follows:

File 1 named DS_1 has the content:

Instructions to merge multiple Excel files into 1 File Picture 1

File 2 named DS_2 has the content:

Instructions to merge multiple Excel files into 1 File Picture 2

Want to aggregate the data from the two files into a single data file named Book1 (the records are on the same Sheet) you do the following:

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

Instructions to merge multiple Excel files into 1 File Picture 3

Step 2: Copy the whole code into Module 1 .

Instructions to merge multiple Excel files into 1 File Picture 4

 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 <= UBound (FilesToOpen) 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 named GopFileExcel () . Because the module contains macros, when you have a warning, click OK .

Instructions to merge multiple Excel files into 1 File Picture 5

Step 4: Create 1 more Module, click Insert -> Module .

Instructions to merge multiple Excel files into 1 File Picture 6

Step 5: Copy the whole code into module 2 -> save and name GopCacSheet () .

Instructions to merge multiple Excel files into 1 File Picture 7

 Sub GopCacSheet () 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). Copied 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 newly created Excel File -> Go to View tab -> View Marco .

Instructions to merge multiple Excel files into 1 File Picture 8

Step 7: Macro dialog box appears -> select module 1 (name GopFileExcel ()) -> Run .

Instructions to merge multiple Excel files into 1 File Picture 9

Step 8: The Browse dialog box appears, select the path to the data files to merge (here files DS_1 and DS_2) -> Open .

Instructions to merge multiple Excel files into 1 File Picture 10

After selecting Open data from 2 corresponding files recorded in sheet 2 and sheet 3 in Book1 file. The final merging of the Sheet into 1 single Sheet.

Step 9: Go to the View tab -> View Macro -> GopCacSheet () -> Run .

Instructions to merge multiple Excel files into 1 File Picture 11

All data in 2 Sheet combined into Sheet 1. Note about the incorrect order you need to enter and align the size of the columns. After editing the results:

Instructions to merge multiple Excel files into 1 File Picture 12

Attention:

- Sheet merge sheet2, sheet 3 is sheet 1.

- Apply with a number of different files, the bigger the number of files, the slower the processing speed.

- In the process of merging data files should not use any data function will cause errors (because the address changes). Should use pure input.

Good luck!