|
需要把多个excel工作簿合并到一个工作簿的多个sheet下,在论坛里找到了强人提供的模板。我自己随意新建了几个excel工作簿,可以合并。但实际应用时,却提示“类型不匹配”。强人提供模板的代码如下,请大家不吝赐教。
自己试了试,只要把第二个EXCEL中的第四行(已标黄)删除或在其后随便输入数据,即可正常工作,但实际工作中,很多个表都删一遍,不太现实。
Sub 合并工作薄()
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String
Dim MyDir As String
MyDir = ThisWorkbook.path & "\"
ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = MyDir
FileName = Dir(path & "\*.xls*", vbNormal)
Do Until FileName = ""
If FileName <> ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address = Range("$A$1").Address Then
Else
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Set Wkb = Nothing
Set LastCell = Nothing
End Sub
|
|