多个excel文件快速合并成一个文件的几种方法

Excel多个文件格子如何合并？非常好用

1.先把所有要合并的EXCEL放到同一目录下.

2.在当前目录下新建一个EXCEL

3.打开新建的EXCEL 按ALT+F11

4.在sheet1里输入

-------------------------------------此行不要复制----------------

Sub 合并工作表()

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 =

Num = 0

Do While MyName<> ""

If MyName<>AWbName Then

Set Wb = Workbooks.Open(MyPath& "\" &MyName)

Num = Num + 1

With Workbooks(1).ActiveSheet

.Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4) For G = 1 To Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1) Next

WbN = WbN&Chr(13) &

Wb.Close False

End With

End If

MyName = Dir

Loop

Range("A1").Select

Application.ScreenUpdating = True

MsgBox "共合并了" &Num& "个工作薄下的全部工作表。如下：" &Chr(13) &WbN, vbInformation, "提示"

End Sub

---------------------------此行不要复制-----------------------

5.关闭Microsoft Visual Basic

6.点击工具-----宏-----安全性改成低(如果已经改成低,此步骤可以省略)

7.点击工具-----宏-----宏再点击执行

8.稍等1~2分钟<注意:EXCEL的总行数不要超过65535行>

楼主幸运，以前我找过，朋友给了段vba代码，很方便的

Sub CombineWorkbooks()

Dim FilesToOpen, ft

Dim x As Integer

Application.ScreenUpdating = False

On Error GoToerrhandler

FilesToOpen = Application.GetOpenFilename _

(FileFilter:="Micrsofe Excel文件(*.xlsx), *.xlsx", _ MultiSelect:=True, Title:="要合并的文件")

If TypeName(FilesToOpen) = "boolean" Then

MsgBox "没有选定文件"

'GoToerrhandler

End If

x = 1

While x <= UBound(FilesToOpen)

Set wk = Workbooks.Open(Filename:=FilesToOpen(x))

wk.Sheets().Move after:=ThisWorkbook.Sheets _ (ThisWorkbook.Sheets.Count)

x = x + 1

Wend

MsgBox "合并成功完成！"

errhandler:

' MsgBoxErr.Description

'Resume errhandler

End Sub

回答人的补充 2009-07-09 10:33

Sub test()

Dim i As Integer, j As Integer

For i = 2 To Sheets.Count

Sheets(i).Activate

edRange.Select Selection.Copy

Sheets(1).Activate

j = Range("a65536").End(xlUp).Row Cells(j + 1, 1).Select ActiveSheet.Paste

Next i

End Sub