如何用宏拆分excel文件

您可以使用Excel VBA宏来拆分Excel文件，以下是一个示例代码：

Sub SplitWorkbook()
    Dim sPath As String
    Dim sht As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim WorkBk As Workbook
    Dim shtName As String
    
    sPath = ThisWorkbook.Path & "\" '设置拆分后文件保存的路径
    
    For Each sht In ThisWorkbook.Worksheets
        shtName = sht.Name
        lastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row '获取最后一行
        i = 2 '从第二行开始
        
        Do While i <= lastRow
            Set WorkBk = Workbooks.Add '新建工作簿
            With WorkBk
                .Sheets(1).Name = shtName '设置工作表名称
                sht.Rows(1).EntireRow.Copy .Sheets(1).Rows(1) '复制表头
                j = 1
                Do While j + i - 1 <= lastRow And j <= 5000 '每个工作簿最多5000行
                    sht.Rows(j + i - 1).EntireRow.Copy .Sheets(1).Rows(j + 1) '复制数据
                    j = j + 1
                Loop
                .SaveAs Filename:=sPath & shtName & "_" & k & ".xlsx", FileFormat:=51 '保存工作簿，格式为xlsx
                .Close
            End With
            i = i + j
            k = k + 1
        Loop
    Next sht
End Sub

以上代码将每个工作表拆分成最多5000行的Excel文件保存到指定的路径下，文件名格式为“原文件名_数字.xlsx”。您可以根据需要修改代码中的路径和行数等参数。