excel根据行数分表

1、点击开发工具→宏→输入宏名称→点击创建

2、输入如下代码：

Dim r, c, i, WJhangshu, WJshu, bt As Long

r = Range("A" & Rows.Count).End(xlUp).Row

b = InputBox("请输入分表行数")

If IsNumeric(b) Then

       WJhangshu = Int(b)

    Else

        MsgBox "输入错误", vbOKOnly, "错误"

        End

End If

c = Cells(1, Columns.Count).End(xlToLeft).Column

bt = 1 '标题行数

'WJhangshu = 50 '每个文件的行数

WJshu = IIf(r - bt Mod WJhangshu, Int((r - bt) / WJhangshu), Int((r - bt) / WJhangshu) + 1)

For i = 0 To WJshu

    Workbooks.Add

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i, String(Len(WJshu), 0)) & ".xlsx"

    Application.DisplayAlerts = True

    ThisWorkbook.ActiveSheet.Range("A1").Resize(bt, c).Copy ActiveSheet.Range("A1")

    ThisWorkbook.ActiveSheet.Range("A" & bt + i * WJhangshu + 1).Resize(WJhangshu, c).Copy _

    ActiveSheet.Range("A" & bt + 1)

    ActiveWorkbook.Close True

Next

3、点击运行出现对话框，输入50并确定，在Excel文件对应的路径下会生成分表文件。

4、分表结果