Question

如何把多个excel表合并到一个工作簿的各个sheet里面

Answer 1

昨天为银行朋友做了一个子行数据复制黏贴到汇总表。代码如下；
Public maxHH As Long
Sub main()
Dim zz As Integer
Dim bj As Boolean
Dim wjmArr(1 To 100) As String
Dim ksHh As Integer
Dim 检测列 As Integer
Dim hzBook As Workbook, zhBook As Workbook
Dim jsHH As Integer
Dim 子表名称 As String
Dim WIND1 As String, WIND2 As String
Dim hhZD
Application.DisplayAlerts = False
ksHh = Cells(3, 2).Value: maxHH = Cells(3, 4).Value
检测列 = Cells(3, 3).Value
Set hhZD = CreateObject("SCRIPTING.DICTIONARY")
Call 读取文件名(wjmArr, bj, zz)
Set hzBook = Workbooks.Open(ThisWorkbook.Path & "\" & wjmArr(1))
WIND1 = hzBook.Name
'清空汇总表
For Each mys In hzBook.Sheets

Range(mys.Cells(ksHh, 1), mys.Cells(maxHH, 100)).Clear
hhZD.Add mys.Name, ksHh
Next mys
'复制支行子表到总表
For i = 2 To zz
Set zhBook = Workbooks.Open(ThisWorkbook.Path & "\" & wjmArr(i))
WIND2 = zhBook.Name
For Each mys In zhBook.Sheets
jsHH = ksHh
Do While mys.Cells(jsHH, 检测列) <> ""
jsHH = jsHH + 1
Loop
If jsHH > ksHh Then
子表名称 = mys.Name
Range(mys.Cells(ksHh, 1), mys.Cells(jsHH - 1, 100)).Copy
Windows(WIND1).Activate
Sheets(子表名称).Activate
ActiveSheet.Cells(hhZD(子表名称), 1).Select
ActiveSheet.Paste
hhZD(子表名称) = hhZD(子表名称) + jsHH - ksHh
End If
Next mys
zhBook.Close
Next i
Application.DisplayAlerts = True
End Sub
Sub 读取文件名(ByRef wjmArr, ByRef bj, ByRef zz)
Dim FILENAME As String
Dim mypath As String
mypath = ThisWorkbook.Path
zz = 1
myfile = Dir(mypath & "\" & "*.xls*")
bj = False
Do While myfile <> ""
If myfile = "" Then
Exit Do '当MyFile为空的时候就说明已经遍历完了，这时退出Do，否则还要运行一遍
End If
If InStr(myfile, "工具") = 0 Then
If InStr(myfile, "汇总") > 0 Then
wjmArr(1) = myfile
bj = True
Else
zz = zz + 1
wjmArr(zz) = myfile
End If
End If
myfile = Dir '第二次读入的时候不用写参数
Loop
End Sub

Answer 2

如果数量不超过十个，还是复制粘贴来的快

Answer 3

编程可以不要手工复制粘贴，几秒-几分钟出来 结果

Answer 4

复制过来就可以