Split Every N Rows From Worksheet Into New Worksheets

[supermat]

I have a large worksheet. Over 20,000 rows. I want to split every 900 rows into new worksheets. I need each worksheet to be 900 rows or less when done. Any macro would be helpful.

 

[Saisai]

How if I want to split in in the next column in the same sheet?
Thanks.

Sure:

Sub test()
Dim lastRow As Long, myRow As Long, mySheet As Worksheet
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 2 To lastRow Step 900
    Set mySheet = Worksheets.Add
    Sheets("Sheet1").Rows(myRow & ":" & myRow + 899).EntireRow.Copy mySheet.Range("A1")
Next myRow
End Sub

Dom

 

[Saeed Rasool Afridi]

Try something like:

Sub test()
Dim lastRow As Long, myRow As Long, myBook As Workbook
lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For myRow = 2 To lastRow Step 900
    Set myBook = Workbooks.Add
    ThisWorkbook.Sheets("Sheet1").Rows(myRow & ":" & myRow + 899).EntireRow.Copy myBook.Sheets("Sheet1").Range("A1")
Next myRow
End Sub

Dom

Hi, I'm not even sure if you'd respond to a thread almost 9 years ago. Your thread helped me but the last sheet the macro tried to create gave me an error that the remaining data did not have sufficient rows in it.

My data has 1048576 rows in it and I had the code to create new sheets every 40,000 rows, the last sheet was blank because 2576 rows were available instead of 40,000.
Can you please help?