怎样编写一个VBA,快速将多个excel工作簿按相同名称的工作表分开汇总到一个新表格里。 求一个VBA，一个文件夹中多个EXCEL工作簿合并成一个工作...
\u600e\u6837\u5feb\u901f\u5730\u628a\u4e00\u4e2aEXCEL\u8868\u683c\u91cc\u7684\u591a\u4e2a\u5de5\u4f5c\u8868\u5206\u5f00\u6210\u5355\u4e2a\u7684\u6587\u4ef61\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
Sub ABCD()
Dim lj As String
Dim dirname As String
Dim nm As String
lj = ActiveWorkbook.Path
nm = ActiveWorkbook.Name
dirname = Dir(lj & "\*.xls")
Cells.Clear
Do While dirname <> ""
If dirname <> nm Then
Workbooks.Open Filename:=lj & "\" & dirname
Workbooks(nm).Activate
Workbooks(dirname).Sheets(1).Range("A4:J15").Copy _
Sheets(1).Range("a65536").End(xlUp).Offset(1, 0)
Workbooks(dirname).Close False
End If
dirname = Dir
Loop
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End Sub
绛旓細瀵逛簬涓鑸敤鎴疯岃█锛岄櫎浜嗗鍒舵瘡涓伐浣滆〃鍚庡啀绮樿创锛屾病鏈夊叾浠栦粈涔堟柟娉曚簡銆傚鏋滃彧鏄悎骞跺皯鏁板嚑涓伐浣滆〃锛岃繖涓柟娉曞緢绠鍗曪紱濡傛灉瑕佸悎骞跺ぇ閲忕殑宸ヤ綔琛紝杩欎釜鏂规硶闈炲父鑰楁椂銆傜幇鍦紝鎴戜滑璋堣皥鍏朵粬蹇鍚堝苟宸ヤ綔琛ㄦ垨宸ヤ綔绨跨殑鏂规硶銆備笅闈㈢殑VBA 浠ｇ爜鑳藉府浣犳妸褰撳墠宸ヤ綔绨块噷鍚勪釜宸ヤ綔琛ㄩ噷鐨勬暟鎹悎骞跺埌涓涓鏂板伐浣滆〃閲屻傚悓鏃堕渶瑕佹敞鎰...
绛旓細4. 浣跨敤VBA瀹忥細濡傛灉闇瑕佽嚜鍔ㄥ寲杩欎釜杩囩▼锛屽彲浠ヤ娇鐢╒BA瀹忋缂栧啓涓涓猇BA瀹忥紝瀹冧細鎵撳紑姣忎釜婧怑xcel鏂囦欢锛屽皢鍏舵暟鎹鍒跺埌鐩爣宸ヤ綔绨夸腑鐨勫悎閫備綅缃傝繖涓畯鍙互閫氳繃涓娆℃墽琛屾潵澶勭悊澶氫釜宸ヤ綔绨夸腑鐨勬暟鎹傝繖浜涙柟娉曞彲浠ユ牴鎹綘鐨勫叿浣撻渶姹傚拰鏁版嵁鐨勫鏉傜▼搴︽潵閫夋嫨銆傚浜庣畝鍗曠殑浠诲姟锛屽鍒剁矘璐村彲鑳借冻澶燂紝浣嗗浜庡鏉傜殑鏁版嵁澶勭悊鍜...
绛旓細鍙互浣跨敤bat鏂囦欢鏉ュ悎骞跺涓狤xcel鏂囦欢銆傞氳繃缂栧啓鎵瑰鐞嗚剼鏈(bat鏂囦欢)锛屽彲浠ュ疄鐜拌嚜鍔ㄥ寲鍦灏嗗涓Excel鏂囦欢杩涜鍚堝苟銆1. 棣栧厛锛屼綘鍙互浣跨敤Excel鑷甫鐨勫畯鍔熻兘鏉ュ疄鐜板涓枃浠剁殑鍚堝苟銆傚彲浠ュ綍鍒朵竴涓畯锛岀劧鍚庡湪bat鏂囦欢涓皟鐢ㄨ瀹忔潵瀹炵幇鏂囦欢鐨勫悎骞舵搷浣溿2. 鍙︿竴绉嶆柟娉曟槸浣跨敤VBA缂栫▼銆備綘鍙互缂栧啓涓涓猇BA瀹忔潵鎵撳紑澶氫釜...
绛旓細濡備綍蹇鍦灏嗗涓Excel鏂囦欢鍚堝苟鍒涓涓Excel鏂囦欢涓 涓銆佹槑纭瓟妗 鍙互浣跨敤Excel鐨“鏁版嵁鍚堝苟”鍔熻兘鎴栬呯涓夋柟鎻掍欢鏉ュ疄鐜板涓狤xcel鏂囦欢鐨勫悎骞躲傚悓鏃讹紝閫氳繃VBA缂栫▼涔熷彲浠ヨ揪鍒板悎骞剁殑鐩殑銆備簩銆佽缁嗚В閲 1. 浣跨敤Excel鐨“鏁版嵁鍚堝苟”鍔熻兘锛氭墦寮涓涓柊鐨凟xcel宸ヤ綔绨裤傞夋嫨涓涓伐浣滆〃锛屼綔涓哄悎骞...
绛旓細鎴戣繖鍎挎湁涓猇BA浠ｇ爜锛屻備綘鎷垮幓鐢ㄥ氨鍙互浜嗐係ub 鍚堝苟褰撳墠鐩綍涓嬫墍鏈夊伐浣滅翱鐨勫叏閮ㄥ伐浣滆〃()Dim MyPath, MyName, AWbNameDim Wb As Workbook, WbN As StringDim G As LongDim Num As LongDim BOX As StringApplication.ScreenUpdating = FalseMyPath = ActiveWorkbook.PathMyName = Dir(MyPath & "\" ...
绛旓細vba鍊掓槸鍙互锛屼笉杩囨病蹇呰浜嗭紝涓嬮潰鏈変袱涓幇鎴愮殑宸ュ叿 1.Excel姹囨讳笓瀹 4.0涓枃鐗瑰埆鐗 1)姹囨绘牸寮忕浉鍚岀殑宸ヤ綔琛 灏嗗涓琛屻佸垪鏍煎紡鐩稿悓鐨勫伐浣滆〃鐨勫唴瀹规眹鎬诲埌鍗曚釜宸ヤ綔琛ㄥ搴斿崟鍏冩牸涓傚弬鍔犳眹鎬荤殑宸ヤ綔琛ㄥ彲浠ュ湪涓涓Excel鏂囦欢涓紝涔熷彲浠ュ湪涓嶅悓Excel鏂囦欢涓傛偍涓嶉渶瑕缂栧啓浠讳綍Excel鍏紡灏辫兘瀹炵幇Excel琛ㄦ牸鏁版嵁鐨蹇...
绛旓細浠ヤ笂浠ｇ爜閫傜敤浜庯紝闄や簡绗琻琛ㄥ锛屽彧鏈涓涓sheet1鐨勬儏鍐点俥xcel涓vba鐨勯拡瀵规ц繕鏄洰寮虹殑锛岄渶瑕佽冭檻鐨勬儏鍐垫槸寰堝鐨勩傛墦寮宸ヤ綔琛紝宸ュ叿锛屽畯锛屽畨鍏ㄦэ紝涓紝纭畾 alt+f11 鑿滃崟锛屾彃鍏ワ紝妯″潡锛屽鍒朵笅闈唬鐮佸埌鍙宠竟绌虹櫧鍖猴紝瀛樼洏閫鍑猴紝閲嶆柊鎵撳紑鏂囦欢锛岄夋嫨鍚敤瀹忥紝鐒跺悗alt+F8 閫夋嫨 澶嶅埗鍚堝苟 杩欎釜瀹忥紝杩愯锛屽嵆鍙 ...
绛旓細sub tt for i= 5 to sheets.count rw=sheets(i).range("a65536").end(xlup).row n=sheets(4).range("a65536").end(xlup).row+1 sheets(i).rows("2:" & rw).copy sheets(4).range("a" & n)rw=nothing n=nothing next end sub ...
绛旓細"+") > 0 Then Set st = Sheets.Add st.Name = Cells(i, 1) j = 1 End If Rows(i).Copy st.Rows(j) j = j + 1 i = i + 1 WendEnd Sub濡傛灉瀹屽叏娌℃湁VBA鍩虹锛岃浣跨敤闄勪欢銆
绛旓細鏂规硶濡備笅锛氭楠/鏂规硶 绗竴姝ワ細鎻掑叆鐢ㄦ埛妯″潡 鍦‥XCEL绐楀彛涓寜ALT+F11缁勫悎閿紝璋冨嚭VBA绐楀彛銆傚湪VBA绐楀彛涓寜涓婇潰鑿滃崟鏍忎腑鐨勨滄彃鍏ワ紞妯″潡鈥濓紝灏变細鍦ㄤ笅闈㈠嚭鐜涓涓绌虹櫧鐨勬ā鍧楃獥鍙锛屾妸浠ｇ爜澶嶅埗绮樿创鍒拌繖涓獥鍙ｄ腑灏辫浜嗐傜浜屾锛缂栧啓浠ｇ爜 澶у鍙互鐪嬪埌锛岃繖涓嚜瀹氫箟鍑芥暟鍏辨湁涓変釜鍙傛暟锛氬弬鏁癮鏄鏌ユ壘鐨勫尯鍩燂紱鍙傛暟b...
