The below will break each sheet into sheets with 400 lines or less
[VBA]Sub BreakASheet(Optional MaxNum As Long = 400)
Dim Ws As Worksheet, mI As Long, NWs As Worksheet
Dim LC As String
With ActiveWorkbook
For Each Ws In .Worksheets
Ws.Activate
mI = LastRow(Ws)
LC = LastColumn(Ws)
If mI > MaxNum Then
While mI > MaxNum
Set NWs = .Worksheets.Add
Ws.Range("A" & CStr(1 + mI - MaxNum), LC & CStr(mI)).Cut NWs.Range("A1", LC & CStr(MaxNum))
mI = mI - 400
Wend
End If
Next
End With
End Sub
Function LastRow(Aws As Worksheet) As Long
LastRow = Aws.Cells(Aws.Rows.Count, "A").End(xlUp).Row
End Function
Function LastColumn(Aws As Worksheet) As String
Dim LC As Long
LC = Aws.UsedRange.Columns(Aws.UsedRange.Columns.Count).Column
If LC < 27 Then
LastColumn = Chr(LC + 64)
End If
End Function[/VBA]