|
表格格式要完全相同。
本帖最后由 1091126096 于 2013-5-26 17:08 编辑
- 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
- '获取当前目录下所有excel文件
- MyName = Dir(MyPath & "" & "*.xls")
- '获取活动excel名称
- AWbName = ActiveWorkbook.Name
- 'Num用以记录合并excel数量
- Num = 0
- '开始合并,合并目录时不处理当前excel
- Do While MyName <> ""
- If MyName <> AWbName Then
- Set Wb = Workbooks.Open(MyPath & "" & MyName)
- Num = Num + 1
- With Workbooks(1).ActiveSheet
- '处理当前工作簿上所有的工作表,其中,除第一个表格以外不复制第一行表头,复制位置在当前表格最下一行
- If Num = 1 Then
- For G = 1 To Sheets.Count
- Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row, 1)
- Next
- Else
- For G = 1 To Sheets.Count
- Wb.Sheets(G).UsedRange.Offset(1, 0).Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1)
- Next
- End If
- WbN = WbN & Chr(13) & Wb.Name
- Wb.Close False
- End With
- End If
- MyName = Dir
- Loop
- Range("A1").Select
- Application.ScreenUpdating = True
- MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示"
- End Sub
复制代码 |
评分
-
查看全部评分
|