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 .
You should read it
- How to fix the SUM function doesn't add up in Excel
- Guidance on how to align Excel correctly
- How to keep Excel and Excel columns fixed?
- What is ### error in Excel? how to fix ### error in Excel
- How to display 0 in front of a number in Excel
- Instructions for searching and replacing in Excel tables
- MS Excel 2007 - Lesson 2: Customizing in Excel
- 3 ways down the line in Excel, line break, down row in 1 Excel cell
May be interested
- Instructions for merging text in Word 2016 easilywhen working with multiple separate word files, combining them into a single document makes it easier to manage the content. compared to manually copying, word 2016 provides an automated tool that helps you do it quickly and accurately.
- How to Use Any DVD Converter to Merge Video Files/Clipsany dvd converter has a variety of features, one of which is merging multiple video files into one continuous video, regardless of file types. this can be incredibly useful if you want to make a video of home movie clips, or want to create...
- Instructions for creating a PDF file from multiple image files on Windowspdfs are designed to support raw text with fonts, images, sound and many other effects. if you have a collection of photos and text documents that you want to scan to your computer in jpegs format, you can combine these files into one pdf file for easy sharing.
- Types of data hiding in Excel - Part end: Hide Excel Filestoday tipsmake will show you the last part: 'hide excel file' in the series 'guide all types of hidden data in excel extremely useful'.
- How to print multiple sheets in Excelprint sheets in excel file can choose to print multiple sheets or print all sheets with a simple and very easy way.
- 10 ways to recover corrupted Excel fileshere are 10 simple ways to recover files, excel spreadsheets are corrupted before considering ... crying or reworking from the beginning.
- How to reduce the size of Excel filesinstructions on how to reduce the size of excel files. in order to reduce the file size, you need to determine the reason why your file size is very large. if you can't find it, you can reduce the file size by doing the following: 1. save the file as a binary file 2. delete
- How to enable or disable automatic downloading of multiple files on Chromeby default, google chrome requires confirmation when a website automatically downloads files continuously. however, if you want to block this action on all pages or blacklist specific sites, here's how.
- how to merge pdf files, merge multiple PDF filesmerge pdf files or merge pdf files together to help you manage small pdf files more easily or save time when having to send multiple files to others. there are many ways to merge multiple pdf files together, in this article tipsmake
- What file is XLL? How to open, edit and convert XLL filesfiles with the .xll extension are excel add-in files. these files offer and provide ways to use third-party tools and functions in microsoft excel, which are not part of this software.