Excel高手，如何使用VBA来合并一个工作表里面的所有工作簿的指定行？ 求一个VBA，一个文件夹中多个EXCEL工作簿合并成一个工作...
excel\u5982\u4f55\u628a\u540c\u4e00\u4e2a\u5de5\u4f5c\u7c3f\u4e2d\uff0c\u6307\u5b9a\u5de5\u4f5c\u8868\u5408\u5e76\u5230\u4e00\u5f20\u5de5\u4f5c\u8868\u4e0a\uff08\u8bf7\u4f7f\u7528VBA\uff09\u6309ALT+F11\u8c03\u51faVBA\u7a97\u53e3\uff0c\u63d2\u5165\u4e00\u4e2a\u6a21\u5757\uff0c\u7136\u540e\u628a\u4e0b\u9762\u7684\u4ee3\u7801\u590d\u5236\u8fdb\u53bb\u3002
Sub hz() Set NewSheet = Sheets.Add(Type:=xlWorksheet) '\u751f\u6210\u4e00\u4e2a\u65b0\u8868 Sheets(NewSheet.Index).Move Before:=Sheets(1) '\u5c06\u6b64\u65b0\u8868\u79fb\u52a8\u5230\u6700\u524d\u9762 For i = 2 To Worksheets.Count Sheets(i).UsedRange.Copy NewSheet.Cells([a65536].End(xlUp).Row + 2, 1) '\u5c06\u5176\u4ed6\u8868\u7684\u5df2\u4f7f\u7528\u533a\u57df\u590d\u5236\u5230\u65b0\u8868\u4e2d Next i MsgBox "\u5408\u5e76\u5b8c\u6210"End Sub\u6267\u884c\u4ee3\u7801\u5373\u53ef
1\u3001\u5c06\u9700\u8981\u5408\u5e76\u7684EXCEL\u6587\u4ef6\u4e0e\u76ee\u7684EXCEL\u6587\u4ef6\u653e\u5728\u4e00\u4e2a\u6587\u4ef6\u5939\u4e0b\u3002
2\u3001 \u6253\u5f00HB.xlsx\uff0c\u5c06\u201c\u5f00\u53d1\u5de5\u5177\u201d\u83dc\u5355\u52a0\u8f7d\u5230EXCEL\u83dc\u5355\u4e0b\u3002
3\u3001\u9996\u5148\u53f3\u952e\u70b9\u51fb\u83dc\u5355\u7a7a\u767d\u5904\uff0c\u9009\u62e9\u201c\u81ea\u5b9a\u4e49\u529f\u80fd\u533a\u201d\uff0c\u5728\u5f39\u51fa\u7684\u5bf9\u8bdd\u6846\u91cc\u9009\u62e9\u4e3b\u9009\u9879\u5361\u3002\u7136\u540e\u52fe\u9009\u201c\u5f00\u53d1\u5de5\u5177\u201d\u3002\u5982\u56fe\u6240\u793a\u3002
4\u3001 \u5236\u4f5c\u5bfc\u5165\u952e\u3002\u70b9\u51fb\u201c\u5f00\u53d1\u5de5\u5177\u201d\u83dc\u5355\uff0c\u9009\u62e9\u201c\u63d2\u5165\u201d--\u201cActivex\u201d\u63a7\u4ef6\u4e0b\u7684\u547d\u4ee4\u6309\u952e\u3002\u5728\u5de5\u4f5c\u8868\u4e2d\u753b\u4e00\u4e2a\u547d\u4ee4\u6309\u94ae\u3002
5\u3001 \u5355\u51fb\u201c\u5f00\u53d1\u5de5\u5177\u201d\u4e0b\u7684\u201c\u8bbe\u8ba1\u6a21\u5f0f\u201d\uff0c\u518d\u53cc\u51fb\u521a\u521a\u521b\u5efa\u7684\u547d\u4ee4\u6309\u94ae\u201cCommandButton1\u201d,\u8fdb\u5165\u4ee3\u7801\u7f16\u8f91\u6846\u3002
6\u3001 \u5c06\u4ee5\u4e0b\u4ee3\u7801\u5168\u90e8\u590d\u5236\u5230\u4ee3\u7801\u6846\u4e2d\u3002
7\u3001 \u5c06HB\u6587\u4ef6\u4fdd\u5b58\u6210\u542f\u7528\u5b8f\u7684\u5de5\u4f5c\u7c3f\u3002\u5173\u95ed\u5f53\u524d\u4ee3\u7801\u6846\uff0c\u56de\u5230EXCEL\u754c\u9762\u3002\u9009\u62e9\u201c\u6587\u4ef6\u201d--\u201c\u53e6\u5b58\u4e3a\u201d--\u201c\u4fdd\u5b58\u7c7b\u578b\u201d\u4e0b\u9009\u62e9\u201c\u542f\u7528\u5b8f\u7684\u5de5\u4f5c\u7c3f\u201d\uff0cOK\u3002
8\u3001\u6253\u5f00HB.xlsm,\u5355\u51fb\u6309\u94ae\u3002\u5219\u51e0\u4e2a\u9700\u8981\u5408\u5e76\u7684EXCEL\u6587\u4ef6\u4e2d\u7684\u5de5\u4f5c\u8868A,B,C\u5408\u5e76\u5230\u4e86HB.xlsm\u8fd9\u4e2a\u6587\u4ef6\u4e2d\u3002
分太少了，发一个之前写过的合并多张Excel到单张Sheet的代码，供参考：
运行主函数 Excels_2_Sheet
Sub deleteCells()
Dim s
Set s = ThisWorkbook.Sheets("Sheet1")
s.Cells.Delete
For Each shp In s.Shapes
shp.Delete
Next shp
Set s = Nothing
End Sub
Sub Excels_2_Sheet()
Dim FilesToOpen
Dim x As Integer, b, ws, ar
'On Error GoTo ErrHandler
Application.ScreenUpdating = False
Call deleteCells
Set b = Worksheets(1)
FilesToOpen = Application.GetOpenFilename(FileFilter:="MicroSoft Excel文件, *.xlsx; *.xls", MultiSelect:=True, Title:="要合并的文件")
If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "没有选中的文件"
GoTo ExitHandler
End If
x = 1
While x <= UBound(FilesToOpen)
'Workbooks.Open Filename:=FilesToOpen(x)
Call pub_wbOpenOrActive2(FilesToOpen(x))
Set ws = Sheets(1)
ws.Activate
With ws
If .UsedRange.Address <> "$A$1" Then
'筛选
Cells.AutoFilter
Range("$A:$U").AutoFilter Field:=15, Criteria1:="=*(111111)*"
'复制
Set ar = Cells.SpecialCells(xlCellTypeVisible).Areas
If ar.Count > 2 Then
If b.Range("A1") = "" Then
ar(1).Copy b.Range("A1")
End If
For j = 2 To ar.Count - 1
ar(j).Copy b.Range("A" & b.Columns(1).Find("*", , , , 1, 2).Row + 1)
'b.Range("A" & Columns(1).Find("*", , , , 1, 2).Row + 1).PasteSpecial Paste:=xlPasteValues
Next j
End If
Set ar = Nothing
End If
End With
Set ws = Nothing
Call pub_wbClose2(FilesToOpen(x))
x = x + 1
Wend
ExitHandler:
Application.ScreenUpdating = True
ThisWorkbook.Activate
ThisWorkbook.Sheets(1).Activate
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
Sub test()
For Each wbook In Workbooks
Debug.Print wbook.Name
Next wbook
End Sub
Sub pub_wbOpenOrActive(ByVal Wbdir As String, ByVal Wbname As String)
' 将某Excel文件打开，或者激活
' 如无此文件，弹出对话框
For Each wbook In Workbooks
If wbook.Name = Wbname Then
wbook.Activate
Exit Sub
End If
Next wbook
If Len(Dir(Wbdir & Wbname)) > 0 Then ' 存在此文件
Workbooks.Open Filename:=Wbdir & Wbname
'Workbooks(Right(WblocalName, Len(WblocalName) - InStrRev(WblocalName, "\"))).Activate
Else
MsgBox "无法找到 " & Wbdir & Wbname
Exit Sub
End If
End Sub
Sub pub_wbOpenOrActive2(ByVal wbLocalName As String)
' 将某Excel文件打开，或者激活
' 如无此文件，弹出对话框
For Each wbook In Workbooks
If wbook.Path & "\" & wbook.Name = wbLocalName Then
wbook.Activate
Exit Sub
End If
Next wbook
If Len(Dir(wbLocalName)) > 0 Then ' 存在此文件
Workbooks.Open Filename:=wbLocalName
'Workbooks(Right(WblocalName, Len(WblocalName) - InStrRev(WblocalName, "\"))).Activate
Else
MsgBox "无法找到 " & wbLocalName
Exit Sub
End If
End Sub
Sub pub_wbClose2(ByVal wbLocalName As String)
' 将某Excel文件关闭
' 如无此文件，忽略
For Each wbook In Workbooks
If wbook.Path & "\" & wbook.Name = wbLocalName Then
wbook.Close False
Exit Sub
End If
Next wbook
End Sub
Sub 合并数据()
Dim n As Integer
Sheets.Add before:=Sheets(1) '新建一个sheet，使得这个sheet用来存放结果，且是第一个sheet
ActiveSheet.Name = "结果"
n = 1
For i = 2 To Sheets.Count '从第二个sheet到最后一个sheet
For r = 10 To Sheets(i).UsedRange.Item(Sheets(i).UsedRange.Count).Row '从第十行到最后一行
If Application.WorksheetFunction.CountA(Sheets(i).Range("b" & r & ":d" & r)) = 3 Then '如果第r行的Br、Cr、Dr单元格的内容都不为空
For c = 2 To 4
Sheets("结果").Cells(n, c) = Sheets(i).Cells(r, c) '保存到第一个sheet的B、C、D列
Next c
n = n + 1
End If
Next r
Next i
End Sub
已上传附件，点击按钮即可验证
你说的不少，看不清楚。建议你出示一个样表说说。
绛旓細浠ｇ爜濡備笅:Sub 鑾峰彇VBA鐗堟湰()MsgBox Application.VBE.Version End Sub 褰撴寜F5閿紝灏辨姤锛氳繍琛屾椂閿欒1004銆傜粡鑰佸笀鎸囩偣锛屽崟鍑绘枃浠垛曗曢夐」鈥曗曚俊浠讳腑蹇冣曗曟墦寮鈥滀俊浠讳腑蹇冣濆璇濇锛屽畯璁剧疆鈥曗曞嬀閫夆滀俊浠诲VBA宸ョ▼瀵硅薄妯″瀷鐨勮闂濄傚啀娆¤繍琛岋紝灏变笉浼氬嚭鐜拌繍琛屾椂閿欒1004鎻愮ず銆傚悗鏉ュ湪寰蒋瀹樻柟涔熺湅鍒颁簡姝ゆ秷鎭...
绛旓細鎵撳紑Excel骞舵寜涓婣LT + F11鎵撳紑VBA缂栬緫鍣ㄣ傚湪VBA缂栬緫鍣ㄤ腑锛岄夋嫨鈥滄彃鍏モ> 鈥滄ā鍧椻濅互鍒涘缓涓涓柊妯″潡銆傚湪妯″潡涓紪鍐欐偍鐨刅BA浠ｇ爜浠ュ垱寤篍xcel鏂囦欢銆備互涓嬫槸涓涓畝鍗曠殑绀轰緥锛屽皢鍦ㄦ柊宸ヤ綔绨夸腑鍒涘缓涓涓伐浣滆〃骞跺～鍏呮暟鎹細vba 澶嶅埗Sub CreateExcelFile()Dim ExcelApp As Object Dim ExcelWorkbook As Object Dim...
绛旓細1銆侀鍏堟垜浠墦寮涓涓伐浣滄牱琛ㄤ綔涓轰緥瀛愩2銆佹垜浠浣跨敤alt+f11缁勫悎蹇嵎閿繘鍏be缂栬緫鍣紝鎻掑叆涓涓柊鐨勬ā鍧楋紝骞跺湪妯″潡涓緭鍏ヤ互涓嬩唬鐮侊細Sub openWorkbook1()Workbooks.Open 闇鎵撳紑鏂囦欢鐨勮矾寰刓鏂囦欢鍚 End Sub 3銆佺湅浠ｇ爜鍐欑殑寰堟槑鐧戒簡锛屾瘮濡傛垜瑕佹墦寮妗岄潰涓婄殑涓涓伐浣滆杽锛岄偅涔堟垜浠氨鏄緭鍏ユ枃浠剁殑璺緞锛屾煡鎵捐矾寰...
绛旓細1銆佹墦寮鐢佃剳妗岄潰涓婄殑excel2007杞欢銆2銆佹墦寮涓涓狤xcel鏂囦欢锛岄紶鏍囧彸鍑讳笅闈㈢殑宸ヤ綔琛紙濡俿heet1锛夈傞夋嫨鈥滄煡鐪嬩唬鐮佲濓紝灏卞彲浠ユ墦寮VBA缂栬緫鐣岄潰銆3銆侀夋嫨濡備笅鍥炬墍绀虹殑涓嬫媺鑿滃崟锛岄夋嫨鈥渨orksheet鈥濄4銆侀夋嫨濡備笅鍥炬墍绀虹殑涓嬫媺鑿滃崟锛岄夋嫨瀵瑰簲鐨勮Е鍙戞ā寮忋傝繖閲岄夋嫨BeforeDoubleClick锛屾剰鎬濆氨鏄锛氬湪鏈伐浣滆〃榧犳爣鍙屽嚮锛...
绛旓細鎵撳紑vba缂栬緫鍣ㄤ緷娆″崟鍑籓ffice鎸夐挳锛岀劧鍚庨夋嫨鈥淓xcel閫夐」鈥濓細鍗曞嚮鈥滃父鐢ㄢ锛岀劧鍚庡嬀閫夆滃湪鍔熻兘鍖烘樉绀衡樺紑鍙戝伐鍏封欓夐」鍗♀濓紝鐒跺悗鍗曞嚮鈥滅‘瀹氣濓細渚濇鍗曞嚮鈥滃紑鍙戝伐鍏凤紝Visual Basic鈥濆悗锛屽嵆鍙墦寮VBA缂栬緫鍣細浣跨敤Vba缂栬緫鍣ㄥ彸鍑婚渶瑕佹彃鍏ヤ唬鐮佺殑琛紝濡傦紝鏈枃瑕佽揪鎴愮殑鐩爣鏄崟鍑讳竴涓寜閽紝鐒跺悗瀹炵幇瀵笲鍒3-11...
绛旓細1. 鎵撳紑 Excel锛鎸変笅 `Alt + F11` 缁勫悎閿墦寮 Visual Basic Editor銆2. 鍦ㄥ乏渚х殑鈥滈」鐩祫婧愮鐞嗗櫒鈥濈獥鏍间腑锛屾壘鍒板苟灞曞紑浣犵殑宸ヤ綔绨匡紙Workbook锛夈3. 鍙屽嚮宸ヤ綔绨跨殑鍚嶇О锛屽湪鈥滀唬鐮佺獥鍙ｂ濅腑杈撳叆浠ヤ笅 VBA 浠ｇ爜锛歚``vba Sub 鑷姩閰嶅()Dim 绗竴鍒 As Range Dim 绗簩鍒 As Range Dim 缁撴灉鍖哄煙 As...
绛旓細Copy '澶嶅埗Sheets(\"Sheet2\").Select'閫変腑琛2Range(Cells(k, 1), Cells(k, 5)).Select '閫変腑琛2鐨勫墠5鍒桝ctiveSheet.Paste Link:=True'绮樿创閾炬帴k = k + 1 '琛2琛屽彿澧炲姞End IfNextApplication.CutCopyMode = False '閫鍑哄鍒剁矘璐存ā寮 杩欏彞鍜屼笅闈㈢殑璇彞涓鸿緟鍔╄鍙ュ彲鍒燙ells(1,...
绛旓細濡傛灉鎴戠悊瑙ｆ纭紝鎮ㄦ兂瑕佸湪涓涓崟鍏冩牸涓姩鎬佹樉绀虹壒瀹氫笟鍔″憳鐨勬垚浜ゅ鎴峰悕绉帮紝鑰屼笉闇瑕浣跨敤VBA浠ｇ爜銆傚湪杩欑鎯呭喌涓嬶紝鎴戝缓璁偍灏濊瘯浣跨敤鍔ㄦ佹暟缁勫叕寮忋備互涓嬫槸濡備綍瀹炵幇鐨勬楠わ細閫夋嫨B1鍗曞厓鏍笺傚湪鍏紡鏍忎腑杈撳叆浠ヤ笅鍏紡锛=FILTER(B3:B11,A3:A11=A1)鎸夐敭鐩樹笂鐨凜trl + Shift + Enter缁勫悎閿紝浠ュ皢鍏紡璁剧疆涓哄姩鎬佹暟缁...
绛旓細鏂规硶/姝ラ濡備笅锛1銆佸弻鍑绘墦寮妗岄潰鐨EXCEL锛2銆佸崟鍑诲彸涓婅鐨凮FFICE鍥炬爣锛屾壘鍒癊XCEL閫夐」锛屽崟鍑绘墦寮锛3銆佸嬀閫夊湪鍔熻兘鍖烘樉绀衡滃紑鍙戝伐鍏封濋夐」鍗★紝鍗曞嚮纭畾鎸夐挳瀹屾垚淇敼銆4銆佸崟鍑昏彍鍗曟爮鐨勫紑鍙戝伐鍏凤紝鐒跺悗鎵惧埌Visual Basic鍗曞嚮鎵撳紑渚垮彲浠ヨ繘琛岀浉鍏崇殑VBA缂栫▼銆
绛旓細瑕浣跨敤VBA缂栧啓绋嬪簭浠ユ牴鎹叧閿瓧浠Excel宸ヤ綔绨夸腑鏌ユ壘闅愯棌宸ヤ綔琛ㄤ腑鐨勬暟鎹苟杩斿洖鏄剧ず锛屽彲浠ユ寜鐓т互涓嬫楠よ繘琛屾搷浣滐細鎵撳紑Excel宸ヤ綔绨匡紝骞舵寜涓婣lt+F11杩涘叆VBA缂栬緫鍣ㄧ晫闈傚湪VBA缂栬緫鍣ㄤ腑锛岄夋嫨鎻掑叆鑿滃崟涓殑妯″潡锛屼互鍒涘缓涓涓柊鐨刅BA妯″潡銆傚湪鏂板缓鐨刅BA妯″潡涓紝缂栧啓浠ヤ笅浠ｇ爜锛歷ba Copy code Sub SearchHiddenSheets()D...
