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!
You should read it
- Merge cells in Excel
- How to merge multiple sheets into 1 PDF file in Excel 2013
- Merge keyboard shortcuts in Excel
- How to split, merge first name in Excel
- How to merge cells in Excel - Instructions to merge cells in Excel 2010, 2013, 2016
- Instructions to merge multiple PDF files into one PDF file
- How to merge multiple PDF files into one PDF file in Mac OS X
- How to merge multiple Word files into 1 file, merge multiple Word documents into one
- How to merge cells in Excel 2003 2007
- Mix text, merge messages, mix Excel tables into Word 2007 documents
- how to merge pdf files, merge multiple PDF files
- How to merge cells in Google Sheets