EXCEL宏根据指定行数来切割excel文件内容成新文件

EXCEL宏根据指定行数来切割excel文件内容成新文件

宏内容

Sub splitexcel()
Dim r, c, i, totalhangshu, fileshu, bt As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
tRow = Val(Application.InputBox("请您输入需要切割的行数？"))
If tRow = 0 Then MsgBox "您未输入行数，程序退出！": Exit Sub
r = Range("A" & Rows.Count).End(xlUp).Row
c = Cells(1, Columns.Count).End(xlToLeft).Column
bt = 1 'title
totalhangshu = tRow 'num
fileshu = IIf(r - bt Mod 20000, Int((r - bt) / totalhangshu), Int((r - bt) / totalhangshu) + 1)
For i = 0 To fileshu
    Workbooks.Add
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i, String(Len(fileshu), 0)) & ".xlsx"
    Application.DisplayAlerts = True
    ThisWorkbook.ActiveSheet.Range("A1").Resize(bt, c).Copy ActiveSheet.Range("A1")
    ThisWorkbook.ActiveSheet.Range("A" & bt + i * totalhangshu + 1).Resize(totalhangshu, c).Copy _
     ActiveSheet.Range("A" & bt + 1)
    ActiveWorkbook.Close True
Next
End Sub

执行过程

1. 打开待处理文件
   
2. 执行宏
   
   
   
3. 观察结果