打开需要合并的EXCEL工作表,新建一个空白sheet,按ALT+F11调出VBA,找到刚新建的空白sheet,鼠标右键:插入->模块,输入以下代码,按F5运行即可。
Sub hb()
Dim bt, i, r, c, n, first As Long
bt = 1
Rem 上一句代码中1表头行数,多行改为对应数值
Cells.Clear
For i = 1 To Sheets.Count
If Sheets(i).Name <> ActiveSheet.Name Then
If first = 0 Then
c = Sheets(i).Cells(1, Columns.Count).End(xlToLeft).Column
Sheets(i).Range("A1").Resize(bt, c).Copy Range("A1")
n = bt + 1: first = 1
End If
r = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
Sheets(i).Range("A" & bt + 1).Resize(r - 1, c).Copy Range("A" & n)
n = n + r - bt
End If
Next
MsgBox "当前工作簿下的全部工作表已经合并完毕!", vbInformation, "提示"
End Sub