excel高手帮忙看一下如何让一个Private Sub CommandButton2
\u5982\u4f55\u7528\u5b8f\u5427\u591a\u4e2aexcel\u5408\u5e76\u4e3a\u4e00\u4e2a\u603b\u8868\uff0cexcel\u4e2d\u7684sheet\u9875\u683c\u5f0f\u4e0d\u53d8\u5de5\u4f5c\u8868\u5408\u5e76\u4e0e\u62c6\u5206\uff1a
Dim rst Dim cnStr As String Dim sql As String Dim sht As WorksheetPrivate Sub CommandButton1_Click() '\u62c6\u5206 Dim i As Integer Dim j As Integer Dim arr Application.DisplayAlerts = False For Each sht In Application.Worksheets If sht.Name "\u8d37\u6b3e\u6c47\u603b\u8868" Then sht.Delete End If Next Application.DisplayAlerts = True cnStr = "Provider='Microsoft.Jet.OLEDB.4.0';Extended Properties='Excel 8.0';Data Source=" & ThisWorkbook.FullName sql = "select distinct \u8003\u6838\u673a\u6784 from [\u8d37\u6b3e\u6c47\u603b\u8868$]" Set rst = CreateObject("ADODB.Recordset") With rst .Open sql, cnStr arr = .GetRows .Close For i = 0 To UBound(arr, 2) ThisWorkbook.Worksheets.Add(after:=Sheets(Sheets.Count)).Name = arr(0, i) '\u5206\u8868\u63d2\u5728\u540e\u9762 Set sht = ActiveSheet 'Set sht = ThisWorkbook.Worksheets.Add '\u5206\u8868\u63d2\u5728\u524d\u9762 'sht.Name = arr(0, i) sql = "select * from [\u8d37\u6b3e\u6c47\u603b\u8868$] where \u8003\u6838\u673a\u6784='" & arr(0, i) & "'" .Open sql, cnStr For j = 1 To .Fields.Count sht.Cells(1, j) = .Fields(j - 1).Name Next sht.Cells(2, 1).CopyFromRecordset rst .Close Next End With Set rst = Nothing
End Sub
Private Sub CommandButton2_Click() '\u5408\u5e76 Rows("2:" & Rows.Count).Delete Set rst = CreateObject("ADODB.Recordset") cnStr = "Provider='Microsoft.Jet.OLEDB.4.0';Extended Properties='Excel 8.0';Data Source=" & ThisWorkbook.FullName With rst For Each sht In Application.Worksheets If sht.Name "\u8d37\u6b3e\u6c47\u603b\u8868" Then sql = "select * from [" & sht.Name & "$]" .Open sql, cnStr Range("A" & Rows.Count).End(xlUp)(2).CopyFromRecordset rst .Close End If Next End With
End Sub
\u5173\u4e8e\u6279\u91cf\u6253\u5f00\u5de5\u4f5c\u8868,\u6279\u91cf\u89e6\u53d1\u5de5\u4f5c\u8868\u4e2d\u7684\u5b8f\u53ef\u7528\u4e0b\u9762\u8fd9\u6bb5\u4ee3\u7801:
Private Sub CommandButton2_Click()
Dim H, ii As Integer
If a = 0 Then
MsgBox "\u627e\u4e0d\u5230\u6587\u4ef6\uff01", , "\u63d0\u793a"
ElseIf a > 0 Then
For ii = 1 To a
Workbooks.Open ListBox1.List(ii - 1)
For H = 5 To 16
name1 = ActiveWorkbook.Name
Application.Run name1 & "!Sheet" & H & ".CommandButton1_Click"
Next H
ActiveWorkbook.Close True '\u4fdd\u5b58\u5173\u95ed\u6253\u5f00\u7684\u5de5\u4f5c\u7c3f
Next ii
End If
Application.Visible = True
End Sub
sheets("sheet2").activate
如果你运行完成后仍需回到第一个表,可以在end sub 前加一句
sheets("sheet1").activate
绛旓細1銆佹壒閲忕粰鏁版嵁鍔犱笂鈥滀竾鍏冣濆崟浣 2銆佹壒閲忚浆鎹㈠皬鏁颁负鐧惧垎鏁帮紝鎸変笅銆怌trl+Shift+5銆 3銆佹瘮鍏紡杩樺揩鐨勬眰鍜屾柟娉曪紝鎸夈怉lt+=銆 4銆佸揩閫熻緭鍏ョ被浼尖001銆002鈥︹︹濊繖鏍风殑缂栧彿 5銆佸崟鍏冩牸鍐呭杩囧鏃跺己鍒舵崲琛岋紝鎸堿lt+鍥炶溅閿 6銆佸鍚嶄腑闂翠笉瑕佸啀鏁茬┖鏍间簡锛屽榻愮敤鈥滃垎鏁ｅ...
绛旓細棣栧厛闇瑕佷拱涓鏈Excel鐨勫叆闂ㄦ暀绋,鎴栬呭弬鍔涓涓姝ｈ鐨勭數鑴戝鏍＄殑鍒濈骇鐝傚湪杩欎釜杩囩▼閲岄潰,瀛︿範鑰呴渶瑕佸ぇ鑷翠簡瑙ｅ埌Excel鐨勫熀鏈搷浣滄柟娉曞拰甯哥敤鍔熻兘,璇稿杈撳叆鏁版嵁,鏌ユ壘鏇挎崲,璁剧疆鍗曞厓鏍兼牸寮,鎺掑簭銆佹眹鎬汇佺瓫閫夊拰淇濆瓨宸ヤ綔绨裤傚鏋滃涔犺呮湁鍏朵粬鐨勫簲鐢ㄨ蒋浠朵娇鐢ㄧ粡楠,鐗瑰埆鏄叾浠朞ffice缁勪欢鐨勪娇鐢ㄧ粡楠,杩欎釜杩囩▼浼氬緢蹇備絾鏄娉ㄦ剰,鐜板湪鐨勪换...
绛旓細杈撳叆浠ヤ笅鍏紡锛岀劧鍚庡悜涓嬪～鍏呭叕寮 =TRIM(MID(SUBSTITUTE(B$2,"銆",REPT(" ",100)),1+(ROW(A1)-1)*100,100))璇﹁闄勫浘
绛旓細1銆侀変腑鍗曞厓鏍硷紱2銆佽彍鍗曗滄暟鎹濃斺斺滄暟鎹湁鏁堟р3銆佸脊鍑烘潵鐨勭獥鍙ｉ夋嫨鈥滄暟鎹湁鏁堟р濃斺斺滆缃4銆佲滃厑璁(A)鈥濋夋嫨鈥滃簭鍒椻濓紱鍙宠竟鈥滄彁渚涗笅鎷夌澶粹濆嬀閫夛紱5銆佲滄潵婧愨濆鐩存帴杈撳叆浣犵殑鍐呭锛屼笉鍚屽唴瀹归」鐢ㄥ崐瑙掔鍙封,鈥濋殧寮锛涘鏋滄潯浠跺湪Excel宸ヤ綔琛ㄤ笂锛屼篃鍙互鐩存帴鐢ㄥ彸闈㈢殑鈥滈夋嫨鍖哄煙鈥濆姛鑳介夋嫨锛6...
绛旓細=TIME(,A1,) 鍐嶈缃涓涓鏃堕棿鏍煎紡灏辫浜 鍦EXCEL涓,鏃堕棿鏄敤1澶╀负24灏忔椂鏉ュ仛渚濇嵁鐨,1绉掑氨鏄敤1/(24*60),灏辨槸鎶1澶╁垎鎴(24*60*60)鍗86400浠,姣忎唤1绉 1鍒嗗氨鏄敤1/(24*60),灏辨槸鎶1澶╁垎鎴(24*60)鍗1440浠,姣忎唤1鍒嗛挓 1灏忔椂灏辨槸1/24,灏辨槸鎶1澶╁垎鎴24浠,姣忎唤1灏忔椂 30/24/60=30*...
绛旓細鏂规硶锛氭彃鍏ユ纭殑鍑芥暟鍚嶏紝鎴栭氳繃瀵瑰紩鐢ㄧ殑鍗曞厓鏍兼垨鍗曞厓鏍煎尯鍩熷畾涔夊悕绉帮紝鍗冲彲瑙ｅ喅闂銆傝繖绉嶉棶棰橈紝鎴戜滑鍦ㄥ崟鍏冩牸涓緭鍏ユ暟瀛楃粡甯搁亣鍒般備富瑕佹槸鎴戜滑杈撳叆鐨勬暟鎹お闀垮紩璧风殑锛岃瘯鎯冲線鏈夐檺瀹归噺鐨勬按缂稿唴涓嶅仠娉ㄦ按锛屾渶鍚庣粨鏋滃綋鐒舵槸姘翠細婧㈠嚭锛屾暟鎹篃涓鏍枫excel琛ㄦ牸鍏紡鏄剧ず涔辩爜鐨勮В鍐虫柟娉 瑙ｅ喅姝ょ被闂锛屽彧闇鐢ㄩ紶鏍囨嫋鍒版敼鍙...
绛旓細甯姪鐨勪汉:41.2涓 鎴戜篃鍘荤瓟棰樿闂釜浜洪〉 鍏虫敞 灞曞紑鍏ㄩ儴 濡備綍鍦excel琛ㄦ牸涓樉绀轰腑鑻辨枃瀵圭収鍛?璇楂樻墜璇锋暀涓涓 闇瑕佸厛鍋氫竴涓腑鑻辨枃瀵圭収琛,姣斿鍦╯heet2琛ㄦ牸鐨凙鍒楄緭鍏ヤ腑鏂,B鍒楄緭鍏ュ搴旂殑鑻辨枃. 鐒跺悗鍦╯heet1琛ㄦ牸涓氨鍙互瀹炵幇浜 B1杈撳叆鍏紡 =if(a1="","",vlookup(a1,sheet2!a:b,2,0)) 鍏紡涓嬫媺 浠ュ悗鍦...
绛旓細绗竴绉嶏細浣跨敤sumif鍑芥暟锛=SUMIFS(E:E,B:B,H2,C:C,I2,D:D,J2)=SUMIFS(F:F,B:B,H2,C:C,I2,D:D,J2)绗簩绉嶏細浣跨敤VLOOKUP鍑芥暟锛=VLOOKUP(H9&I9&J9,IF({1,0},B:B&C:C&D:D,E:E),2,0) 銆愭暟缁勫叕寮忔寜ALT+SHIFT+鍥炶溅缁撴潫銆=VLOOKUP(H9&I9&J9,IF({1,0},B:B&C:C&D:D,F:...
绛旓細1锛 鍗曞嚮鑿滃崟鏍忊滄枃浠垛斺旈〉闈㈣缃濓紝濡備笅鍥撅細2锛屽嚭鐜伴〉闈㈣缃獥鍙ｏ紝鍗曞嚮鈥滃伐浣滆〃鈥濋夐」锛屾壘鍒扳滄墦鍗版爣棰樷濓紝鐒跺悗鍗曞嚮鈥滈《绔爣棰樿鈥濆彸杈圭殑甯︾孩鑹茬澶寸殑鎸夐挳锛屽涓嬪浘锛氳繖鏃讹紝宸茬粡缂╁皬鎴愬涓嬪浘鎵绀虹殑鈥滈〉闈㈣缃旈《绔爣棰樿鈥濈獥鍙ｏ紝杩欎釜绐楀彛濡傛灉褰卞搷鍒颁笅涓姝ュ伐浣滄椂鐨勮绾匡紝鍙互闅忔剰鎷栧姩瀹冦傛妸榧犳爣绉诲埌...
绛旓細涓銆 Excel 365鐗堟湰瓒呯骇鏂囨湰杩炴帴鍑芥暟鐨勬柟娉 濡傚浘锛岀洿鎺ョ敤textjoin鍑芥暟锛欱1=TEXTJOIN("銆",1,B2:B5)鍙虫媺 浜屻佽緟鍔╁垪娣诲姞鏍囩偣鐨勬柟娉曪細鍦ㄦ暟鎹彸渚х┖鐧藉垪涓寜2銆4銆6鍋舵暟锛堜篃鍙互鏄1銆3銆5濂囨暟鈥斺斿彧瑕侀殧涓涓暟锛夛紝鏁版嵁琛屽皯鏃跺彲鐩存帴杈撳叆锛屾暟鎹澶氭椂锛岃緭鍏ヤ袱涓悗濉厖锛涗笅闈㈢殑绌鸿涓寜3銆5銆7濂囨暟锛...
