合并多工作簿中指定工作表

huaichuan1

Sub mergeonexls() '合并多工作簿中指定工作表

On Error Resume Next

Dim x As Variant, x1 As Variant, w As Workbook, wsh As Worksheet

Dim t As Workbook, ts As Worksheet, l As Integer, h As Long

Application.ScreenUpdating = False

Application.DisplayAlerts = False

x = Application.GetOpenFilename(FileFilter:="Excel文件 (*.xls; *.xlsx),*.xls; *.xlsx,所有文件(*.*),*.*", _

       Title:="Excel选择", MultiSelect:=True)

Set t = ThisWorkbook

Set ts = t.Sheets(1) '指定合并到的工作表，这里是第一张工作表

l = ts.UsedRange.SpecialCells(xlCellTypeLastCell).Column

For Each x1 In x

If x1 <> False Then

Set w = Workbooks.Open(x1)

Set wsh = w.Sheets(1) '指定所需合并工作表，这里是第一张工作表

h = ts.UsedRange.SpecialCells(xlCellTypeLastCell).Row

If l = 1 And h = 1 And ts.Cells(1, 1) = "" Then

wsh.UsedRange.Copy ts.Cells(1, 1)

Else

wsh.UsedRange.Copy ts.Cells(h + 1, 1)

End If

w.Close

End If

Next

Application.ScreenUpdating = True

Application.DisplayAlerts = True

End Sub

希望a列加入工作薄的名称 新建文件夹.zip (50.87 KB, 下载次数: 37)

2018-7-18 09:22 上传

点击文件名下载附件

代码是网上找来的VBA不会编辑谢谢了