一个Excel工作簿最多可以有多少个sheet？在许多sheet的时候如何查找

没发现过这种情况，你关机重启一下试试

昨天为银行朋友做了一个子行数据复制黏贴到汇总表。代码如下；
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

可以呀，你得把另一个工作簿打开，在移动或复制工作表的下面工作簿下拉框里选择你要的工作簿名，勾选建立副本，确定就行了