How to Split a list?

Try this macro in the attached workbook.

Sub SplitData()
Dim lngLastRow As Long
Dim lngIndex As Long

lngLastRow = Range("A65536").End(xlUp).Row

For lngIndex = 2 To lngLastRow Step 350
    Sheets("Sheet1").Range("A1:F1", "A" & lngIndex & ":F" & lngIndex + 349).Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Paste
Next
End Sub

Select allOpen in new window

BTW your original file would be much smaller if you deleted all or most of the blank rows in Sheet1.
Q-28248378.xlsm

Hi,
When I run this only sheet2 has 350 rows the other sheets has over 700. I was looking for it to be like the "after" file I attached only 350 rows per sheet or less.
Thanks

Building on MartinLiss' code, try something like:

Option Explicit

Sub SplitData()
Dim lngLastRow As Long
Dim lngIndex As Long

lngLastRow = Range("A65536").End(xlUp).Row

For lngIndex = 2 To lngLastRow Step 350
    Sheets("Sheet1").Range("A1:F1", "A" & lngIndex & ":F" & lngIndex + 349).Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Paste
    Debug.Print lngIndex & "  " & lngIndex + 349
    Sheets("Sheet1").Range("A2:F351").Delete
Next
End Sub

Select allOpen in new window

This works perfect!
Thanks

You're welcome and I'm glad I was able to help.  And I'm sorry I didn't notice that the first time.

Marty - MVP 2009 to 2013