Sub 分表() Dim Pat As String, M As String, Bm As Workbook Dim H As Long, H1 As Long, Sa As Long c = Application.InputBox(Prompt:="请输入目标参照列数字:", Type:=1) Dim Arr As Variant Application.ScreenUpdating = False Pat = ThisWorkbook.Path & "\" With Sheet1 H = .Range("A65536").End(xlUp).Row For H1 = 2 To H If M = "" Then M = Cells(H1, c) '记录要生成的工作薄 名称 If Cells(H1, c) = M Then If Sa = 0 Then Sa = H1 '记录起 始位置 If Cells(H1 + c, 1) <> M Then Set Bm = Workbooks.Add(1) '新建一个工作薄,且只有一个工作表 Bm.SaveAs Pat & M '新建的工作薄 命名另存 With Bm.Sheets(1).Range("A1") .Resize(, 7).Value = Sheet1.Range("A1:G1").Value '写入表头 .Offset(1).Resize(H1 - Sa, 7) = Sheet1.Range(Sheet1.Cells(Sa, "A"), Sheet1.Cells(H1, "G")).Value '写入数据 End With Bm.Close True '关闭这个新建的工作薄 M = "" '清空 表名 Sa = 0 '清空 起始位置 End If End If Next End With Application.ScreenUpdating = True MsgBox "工作薄已生成,并已保存到 " & Pat & " 文件夹中", , "完成" End Sub |