1、首先我们要右键单击sheet1，选择查看代码
2、然后选中左边的sheet1输入代码：Sub NewSheets()Dim d 钽吟篑瑜As Object, sht As Worksheet荑樊综鲶, arr, brr, r, kr, i&, j&, k&, x&Dim Rng As Range, Rg As Range, tRow&, tCol&, aCol&, pd&Application.ScreenUpdating = FalseApplication.DisplayAlerts = FalseSet d = CreateObject("scripting.dictionary")Set Rg = Application.InputBox("请您框选拆分依据列！只能选择单列单元格区域！", Title:="提示", Type:=8)tCol = Rg.ColumntRow = Val(Application.InputBox("请您输入总表标题行的行数？"))If tRow = 0 Then MsgBox "您未输入标题行行数，程序退出！": Exit SubSet Rng = ActiveSheet.UsedRangearr = RngtCol = tCol - Rng.Column + 1aCol = UBound(arr, 2)For i = tRow + 1 To UBound(arr)If Not d.exists(arr(i, tCol)) Thend(arr(i, tCol)) = iElsed(arr(i, tCol)) = d(arr(i, tCol)) & "," & iEnd IfNextFor Each sht In WorksheetsIf d.exists(sht.Name) Then sht.DeleteNextkr = d.keysFor i = 0 To UBound(kr)If kr(i) <> "" Thenr = Split(d(kr(i)), ",")ReDim brr(1 To UBound(r) + 1, 1 To aCol)k = 0For x = 0 To UBound(r)k = k + 1For j = 1 To aColbrr(k, j) = arr(r(x), j)NextNextWith Worksheets.Add(, Sheets(Sheets.Count)).Name = kr(i).[a1].Resize(tRow, aCol) = arr.[a1].Offset(tRow, 0).Resize(k, aCol) = brrRng.Copy.[a1].PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False.[a1].SelectEnd WithEnd IfNextSheets(1).ActivateSet d = NothingErase arr: Erase brrMsgBox "数据拆分完成！"Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEnd Sub
3、直接关闭代码编辑，查找选择查看宏，点击执行。
4、然后选择我们要拆分的依据条件，然后输入行数，点击确定，拆分完成。
