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:
File 2 named DS_2 has the content:
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 .
Step 2: Copy the whole code 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 <= 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 .
Step 4: Create 1 more Module, click Insert -> Module .
Step 5: Copy the whole code into module 2 -> save and name GopCacSheet () .
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 .
Step 7: Macro dialog box appears -> select module 1 (name GopFileExcel ()) -> Run .
Step 8: The Browse dialog box appears, select the path to the data files to merge (here files DS_1 and DS_2) -> Open .
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 .
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:
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!