– 1. 将一个Excel内容自动均分到多个中,每个h+1行(复制表头)

Sub aa()

Dim newbook As Workbook
a = ThisWorkbook.Name
b = ActiveSheet.Name

h = InputBox("input number")

Application.ScreenUpdating = False

Dim rowcount, page, pagemod
rowcount = Range("a65536").End(xlUp).Row - 1
pagemod = rowcount Mod h
If pagemod = 0 Then
    page = rowcount \ h
Else
    page = (rowcount \ h) + 1
End If

For n = 1 To page
Windows(a).Activate
Sheets(b).Activate

Set newbook = Workbooks.Add
With newbook


Union(Rows(1), Rows((h * (n - 1) + 2) & ":" & (h * (n - 1) + h + 1))).Copy

newbook.Activate
ActiveSheet.Paste

.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(a, ".xlsx", "") & n & ".xlsx"
.Close
End With
Next n

Application.ScreenUpdating = True
End Sub

– 2. 将一个Excel内容自动均分到多个中,每个h+1行(复制表头)(自增行自动从1递增)

Sub aa()

Dim newbook As Workbook
a = ThisWorkbook.Name
b = ActiveSheet.Name

h = InputBox("input number")

Application.ScreenUpdating = False

Dim rowcount, page, pagemod, autoid
rowcount = Range("a65536").End(xlUp).Row - 1
pagemod = rowcount Mod h
If pagemod = 0 Then
    page = rowcount \ h
Else
    page = (rowcount \ h) + 1
End If

For n = 1 To page

If n = page Then
    autoid = rowcount - h * (n - 1) + 1
Else
    autoid = h + 1
End If

Windows(a).Activate
Sheets(b).Activate

Set newbook = Workbooks.Add
With newbook

newbook.Activate
Rows(1).Copy
ActiveSheet.Range("A1").PasteSpecial xlPasteAll
Range("B" & (h * (n - 1) + 2) & ":F" & (h * (n - 1) + autoid + 1)).Copy
ActiveSheet.Range("B2").PasteSpecial xlPasteAll

ActiveSheet.Range("A2").Value = 1
ActiveSheet.Range("A2").AutoFill Destination:=Range("A2:A" & autoid)

.SaveAs Filename:=ThisWorkbook.Path & "\" & Replace(a, ".xlsx", "") & n & ".xlsx"
.Close
End With
Next n

Application.ScreenUpdating = True
End Sub

参考:
https://zhidao.baidu.com/question/2013860089247981428.html
http://www.360doc.com/content/19/0527/17/26751020_838561062.shtml
https://www.cnblogs.com/wzh313/articles/9737573.html