excel用vba如何按某列对一个工作簿里的两个工作表进行分表拆成多个工作簿？ excel中怎么样按某一列的数据自动分成几张表格（要在不同的...
EXCEL\u5982\u4f55\u5c06\u4e00\u4e2a\u5de5\u4f5c\u8868\u6839\u636e\u6761\u4ef6\u62c6\u5206\u6210\u591a\u4ef6\u5de5\u4f5c\u8868\u5982\u4f55\u5c06\u4e00\u5f20\u5de5\u4f5c\u8868\u62c6\u5206\u6210\u591a\u4e2a\u5de5\u4f5c\u8868\uff1f
1 \u4e0b\u8f7d\u9644\u4ef6\uff0c\u70b9\u51fb\u6309\u94ae
2 \u6309\u8981\u6c42\u6839\u636eC\u5217\u5728\u672c\u5de5\u4f5c\u7c3f\u8def\u5f84\u4e0b\u521b\u5efaN\u4e2a\u5de5\u4f5c\u7c3f\u5e76\u5bfc\u5165\u6709\u5173\u6570\u636e\uff0c\u65b0\u521b\u5efa\u7684\u5de5\u4f5c\u7c3f\u6309\u73ed\u7ea7\u540d\u79f0\u547d\u540d
3 \u4e0d\u660e\u767dhi\u6211
这下子明白了，部门都在A列，表头只有一行，代码如下图：
下面的文字仅供参考，浏览器可能会偷吃字符：
Option Explicit
Sub 拆分()
Dim depts, dept, arr, i, j, st, wb, st2
Set depts = CreateObject("scripting.dictionary")
'第一次扫描，获得所有部门清单
For Each st In Sheets
arr = st.UsedRange
For i = 2 To UBound(arr)
dept = Trim(arr(i, 1))
If dept <> "" Then depts(dept) = True
Next i
Next st
'第二次扫描，生产各部门文件
Set wb = ThisWorkbook
For Each dept In depts.keys
With Workbooks.Add
For Each st In wb.Sheets
arr = st.UsedRange
Set st2 = .Sheets.Add(after:=.Sheets(.Sheets.Count))
st2.Name = st.Name
j = 0
For i = 1 To UBound(arr)
If i = 1 Or Trim(arr(i, 1)) = dept Then
j = j + 1
st.Rows(i).Copy st2.Rows(j)
End If
Next i
Next st
.SaveAs wb.FullName & "." & dept & ".xlsx"
.Close
End With
Next dept
End Sub
Option Explicit
Sub 按部门分表()'各个部门是连续的
Dim s As Long, infor
Dim i As Long
Dim Bool As Boolean
With ThisWorkbook.Worksheets("人员信息")
Bool = True
s = 2
For i = 2 To .Range("A" & .Cells.Rows.Count).End(xlUp).Row + 1
If Bool Then
infor = .Cells(s, 1)
Bool = False
End If
If .Cells(i, 1) <> infor Then
Workbooks.Add
.Rows("1:1").Copy ActiveWorkbook.Sheets(1).Range("A1")
.Rows(s & ":" & i - 1).Copy ActiveWorkbook.Sheets(1).Range("A2")
ActiveWorkbook.SaveAs Filename:="C:\Users\qq196\Desktop\123\" & .Cells(i - 1, 1) & ".xlsx"
ActiveWorkbook.Close
s = i
Bool = True
End If
Next i
End With
End Sub
绛旓細=MOD(SUMPRODUCT(--MID(A1:A$5,1,1)),10)&MOD(SUMPRODUCT(--MID(A1:A$5,2,1)),10)&MOD(SUMPRODUCT(--MID(A1:A$5,3,1)),10)寰涓嬫媺 涓瀹氳鐢╲ba鍟 Sub xx() a = [a5].Text n = Len(a) For i = 4 To 1 Step -1 b = Cells(i, 1).Text c = "" F...
绛旓細鎴戝皾璇曚簡鍑犵鎿嶄綔锛屾病鏈夋垚鍔燂紝鎵浠ュ氨鍙兂鍒颁簡VBA缂栫▼銆備笅闈㈠仛浜嗕釜渚嬪瓙 瑕佹眰灏嗙粨鏋滄斁鍦‥鍒楀拰F鍒楃涓姝ワ細鎸堿lt+F11缁勫悎閿墦寮VBA鐨勭紪杈戝櫒绗簩姝ワ細鍦ㄧ紪杈戝櫒涓緷娆″崟鍑昏彍鍗 鎻掑叆-妯″潡绗笁姝ワ細鍦ㄦā鍧椾腑缂栧啓浠ヤ笅浠ｇ爜锛堟墜鏈虹紪鍐欏お鎱㈡墍浠ョ収鐩稿搱锛夌鍥涙锛氱紪鍐欏畬浜嗗氨鎸塅5杩愯鍗冲彲鏈缁堟晥鏋滃浘 鏉ュ紶鍏ㄥ浘 璐硅剳...
绛旓細Sub GetData()Dim arr, brr(), buf(), rlt()Dim i As Long, j As Long, k As Long, m As Long, n As Long, x As Long, y As Long arr = Sheets("Sheet2").Range("a2:e13")ReDim brr(LBound(arr, 2) To UBound(arr, 2), LBound(arr) To UBound(arr))For i = L...
绛旓細vba鏈変釜浼樼偣锛屽氨鏄彲浠ュ綍鍒跺畯 鎵浠ユゼ涓荤殑杩欎釜鎺掑簭锛屽畬鍏ㄥ彲浠ュ綍鍒朵竴娈垫帓搴忕殑瀹 鐒跺悗鏍规嵁褰曞埗鐨勭粨鏋滃啀杩涗竴姝ュ鐞嗗嵆鍙
绛旓細浠ヤ笅浠ｇ爜鍙婃敞閲婁负灏咥鍒涓瓑浜1鐨勫崟鍏冩牸锛屾彁鍙栧埌B鍒椾腑锛歋ub main()Dim i For i = 1 To Range("A65536").End(3).Row 'i浠1鍒癆鍒楁渶鍚庝竴涓潪绌哄崟鍏冩牸鐨勮鍙峰惊鐜 If Cells(i, "A").Value = 1 Then '濡傛灉A鍒楃殑鍊间负1 Cells(i, "B") = Cells(i, "A") '鍒橞鍒楀搴斿崟鍏冩牸鐨勫...
绛旓細Option ExplicitSub 鎵鏈夎〃鍘婚噸()Dim st As Worksheet, arr, i&, rlt$(1 To 1000000, 1 To 1), n&, j&, isnew'缁熻缁撴灉鍒版暟缁剅ltFor Each st In SheetsIf st.UsedRange.Rows.Count > 1 Thenarr = st.UsedRangeFor i = 1 To UBound(arr)arr(i, 1) = Trim(arr(i, 1))If arr(...
绛旓細浠ｇ爜锛歋ub a()Dim i As LongDim j As LongDim js As Longjs = 0 For i = 1 To 65536 If Cells(i, 1) = "" Then Exit For End If j = 1 Do If Cells(j, 2) = "" Then Exit Do End If If Cells(i, 1) = Cells(j, 2) Then js =...
绛旓細With Cells(1, A).CurrentRegion 'A涓虹鍑犲垪锛屼笉杩囦娇鐢ㄦ椂瑕佹崲鎴愭暟瀛楋紙濡侫鍒楀簲鍐欐垚1锛.Sort Key1:=.Cells(1, A), order1:=xlAscending, Header:=xlYes End With 绗竴琛屼负鍒楀悕绉帮紝涓嶅弬涓庢帓搴
绛旓細鍋囪 A 鍒楁渶鍚庝竴涓崟鍏冩牸鎵鍦ㄧ殑琛屾暟灏忎簬 B 鍒楁渶鍚庝竴涓崟鍏冩牸鎵鍦ㄧ殑琛屾暟銆傞偅涔堬細Option Explicit Private Sub CommandButton1_Click()Dim A_LastRow As Long, B_LastRow As Long Application.EnableEvents = False Application.ScreenUpdating = False With Sheet1 '鍋囪鍦ㄥ伐浣滆〃 Sheet1 涓繘琛屾搷浣...
绛旓細浠ｇ爜濡備笅锛歋ub Macro1()Dim i For i = 1 To 2000 If Sheets(1).Cells(i, "I") = 4 Then Sheets(2).Cells(3, "B") = Sheets(1).Cells(i, "J"): Exit For Next i Sheets(2).Select End Sub
