How to merge multiple excel tables into one table

Quickly merge excel together to facilitate analysis and statistics, which can be achieved by using the macro calculation of the excel table. The method is as follows:

1. We need to put multiple excel tables in the same folder and create a new excel in this folder. As shown in the picture:

#2. Use Microsoft Excel to open the newly created excel sheet, right-click sheet1, find "View Code", and click in. After entering, you will see the macro calculation interface. As shown in the picture:

3. Then we copy the following macro calculation codes, and then find the "Run Subprocess/" under "Run" on the toolbar. User Form", the code is as follows, as shown in the figure:

Dim MyPath, MyName, AWbName
Dim Wb As Workbook, WbN As String
Dim G As Long
Dim Num As Long
Dim BOX As String
Application.ScreenUpdating = False
MyPath = ActiveWorkbook.Path
MyName = Dir(MyPath & "\" & "*.xls")
AWbName = ActiveWorkbook.Name
Num = 0
Do While MyName <> ""
If MyName <> AWbName Then
Set Wb = Workbooks.Open(MyPath & "\" & MyName)
Num = Num + 1
With Workbooks(1).ActiveSheet
.Cells(.Range("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
For G = 1 To Sheets.Count
Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)
Next
WbN = WbN & Chr(13) & Wb.Name
Wb.Close False
End With
End If
MyName = Dir
Loop
Range("B1").Select
Application.ScreenUpdating = True
MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"
End Sub

4. After running, wait for about 10 seconds. When the running is completed, that is, after the merger is completed, there will be Tip, just click OK.

#5. Check the merged data. There are more than 5,000 rows, which is the result of merging data from 17 excel tables in the same folder. The effect is shown in the figure.

Recommended related articles: ​
1.How to merge multiple sheets into one in excel
2.How to merge table contents?

For more Excel-related technical articles, please visit the Excel Tutorial column to learn!

The above is the detailed content of How to merge multiple excel tables into one table. For more information, please follow other related articles on the PHP Chinese website!