How to merge multiple sheets into one in excel

In order to merge countless excel table files with the same data format into one excel workbook to facilitate subsequent screening, data extraction, analysis, etc.

It needs to be done in two steps:

The first step: merge all excel into one excel; the contents of the multiple tables just now are transformed into the present A table and multiple sheets below.

Step 2: Merge multiple sheets in this excel into one sheet.

Required basic software: excel software with VBA, you can use Microsoft's office that includes VBA, or you can use the professional version of domestic wps.

The specific steps are described below

Merge different tables into one table with many sheets

1. Create a new one Workbook, name it your merged name.

2. Open this workbook.

3. Right-click on any worksheet label under it and select "View Code". (This button of wps is under the development tools)

4. Paste the following code in the open VBA editing window:

Sub Worksheet merge()

Dim FileOpen
Dim X As Integer
Application.ScreenUpdating = False
FileOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel文件(*.xls),*.xls", MultiSelect:=True, Title:="合并工作薄")
X = 1
While X <= UBound(FileOpen)
Workbooks.Open Filename:=FileOpen(X)
Sheets().Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
X = X + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
Exit Sub
errhadler:
MsgBox Err.Description
End Sub

5. Close the VBA editing window.

6. In excel, go to Tools---Macros---Macros, select "Merge Worksheets Between Workbooks", and then "Execute".

7. In the dialog window that opens, select the worksheets you want to merge. If there are many, you can put them in a folder, and then select them all.

8. Wait. . . . OK!

2 Merge many sheets in one sheet into one sheet

1. In a workbook containing multiple sheets (for example, after merging multiple workbooks, n sheets workbook), create a new sheet

2. Right-click on the newly created sheet label and select "View Code" (the button of wps is under the development tools)

3 . Paste the following code in the open VBA editing window:

Sub Merge all worksheets under the current workbook ()

Application.ScreenUpdating = False
For j = 1 To Sheets.Count
If Sheets(j).Name <> ActiveSheet.Name Then
X = Range("A65536").End(xlUp).Row + 1
Sheets(j).UsedRange.Copy Cells(X, 1)
End If
Next
Range("B1").Select
Application.ScreenUpdating = True
MsgBox "当前工作簿下的全部工作表已经合并完毕！", vbInformation, "提示"
End Sub

Run, wait... Merged A prompt will pop up after that.

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