Excel总表按条件批量拆分多表（一表拆分多表）

1、首先打开上例文件，如下图。

3、然后插入了一个模块1，在代码框中复制如下代码：Option Base 1Sub 批量拆分表()'2020-2-13 21:23:48 Dim r As Long, mb(), i As Long, j As Long, wt1 As Worksheet, wt2 As Worksheet, k1 As Long, k2 As Long r = Cells(Rows.Count, 3).End(xlUp).Row j = 0 Set wt1 = ActiveSheet ReDim mb(r, 2) For i = 5 To r If Cells(i, 2).Value = "户主" Then j = j + 1 mb(j, 1) = i mb(j, 2) = Cells(i, 3).Value End If Next i wt1.Copy After:=wt1 Set wt2 = ActiveSheet Rows("5:" & r).Delete For i = 1 To j wt2.Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = mb(i, 2) If i = j Then wt1.Rows(mb(i, 1) & ":" & r).Copy ActiveSheet.Cells(5, 1) Else wt1.Rows(mb(i, 1) & ":" & mb(i + 1, 1) - 1).Copy ActiveSheet.Cells(5, 1) End If k1 = 5 k2 = 0 Do While Cells(k1, 1).Value <> "" k2 = k2 + 1 Cells(k1, 1).Value = k2 k1 = k1 + 1 Loop Next i wt2.DeleteEnd Sub

5、回到工作表窗口，运行【批量拆分表】宏（菜单栏中点【视图】中下列表中【宏】列表【查看宏(V)】打开宏对方框，选该宏名），运行过程如下图。

7、如果觉得这篇经验帮到了您，请点击下方的 “投票点赞" 或者“收藏”支持我！还有疑问的话可以点击下方的 “我有疑问”，谢谢啦！