How to split data into multiple worksheets by rows count in Excel?

 When I faced such issue recently, I approached our Big Daddy - Google and found this method easily working for me. It is about using the VBA macro to manipulate the spreadsheet into multiple worksheets of desired rows. It saved a lot of time for me and I am sure that it will come handy for you too at some point of time. Best part of this is that you don't need any third party tool for this splitting purpose.

The following VBA code can help you split the rows into multiple worksheets by rows count, do as follows:

1. Hold down the ALT + F11 key to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Sub SplitData()
'Updateby20140617
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Then press F5 key to run the code, and a dialog pops out for selecting a range to split, and then click OK

4. In another dialog for you to specify the rows count.

5. Click OK, and the range are split into multiple sheets by the rows count.

Note: The split worksheets are placed at the back of the master worksheet.

Hope this post is helpful to you.