Skip to main content

Split data into multiple worksheets by rows count in Excel

Author: Xiaoyang Last Modified: 2024-02-20

Efficiently dividing a large data range into multiple Excel worksheets based on a specific row count can streamline data management. For instance, splitting a dataset every 5 rows into multiple sheets can make it more manageable and organized. This guide offers two practical methods to accomplish this task swiftly and easily.

Split data into multiple worksheets by rows count with VBA code

Split data into multiple worksheets by rows count with Kutools for Excel

Split data into multiple worksheets by rows count with VBA code

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.

VBA: Split data into worksheets by rows count in Excel

Sub Splitdatabyrows()
'Updated by Extendoffice 
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
Dim xTRg As Range
Dim xNTRg As Range
Dim xIER
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set xTRg = Application.InputBox("Please select the header row:", xTitleId, "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set WorkRng = Application.InputBox("Please select the data range(exclude the header row):", xTitleId, WorkRng.Address, Type:=8)
If TypeName(WorkRng) = "Nothing" Then Exit Sub
SplitRow = Application.InputBox("Split Row Num", xTitleId, Type:=1)
If SplitRow = 0 Then Exit Sub
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
xIER = WorkRng.Rows.Count
xIER = WorkRng.Row + xIER - 1
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (xIER - xRow.Row + 1) < SplitRow Then
        resizeCount = (xIER - xRow.Row + 1)
    End If
    Set xWs = Application.Worksheets.Add(after:=Application.Worksheets(Application.Worksheets.Count))
    If xIER > (xRow.Row + SplitRow - 1) Then
        xWs.Name = xRow.Row & " - " & (xRow.Row + SplitRow - 1)
    ElseIf xIER = xRow.Row Then
        xWs.Name = xRow.Row
        xWs.Name = xRow.Row & " - " & xIER
    End If
    Set xNTRg = Application.ActiveSheet.Range("A1")
    Set xRow = xRow.Offset(SplitRow)
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

3. Then, press F5 key, in the popped up dialog box, select the header row, see screenshot:

4. Then, click OK, and in the second prompt box, select the data range that you want to split by rows count, see screenshot:

5. Go on clicking OK button, in the third prompt box, enter the number of rows that you want to split by, see screenshot:

6. Then, click OK button, the data has been split into multiple worksheets based on the number of rows, see screenshot:

Split data into multiple worksheets by rows count with Kutools for Excel

If you think the VBA code is complex, Kutools for Excel offers a quick and intuitive solution to efficiently address the issue of splitting data. This Split Data feature simplifies the operation process, allowing users of any skill level to easily divide large datasets into multiple worksheets without the need for in-depth programming knowledge, facilitating effective data management.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

If you have installed Kutools for Excel, please do as follows:

1. Select the range of data that you want to split.Then, click Kutools Plus > Split Data, see screenshot:

3. In the Split Data into Multiple Worksheets dialog box:

  • Select Fixed rows from Split based on option, and enter the number of rows count that you want to split for each worksheets.
  • If your data has headers and you want to insert them into each new split worksheet, please check My data has headers option. (Tip: you can enter the number of the herder rows based on your data.)
  • Then you can specify the split worksheet names, under the New worksheets name section, select the worksheet names rules from the Rules drop down list, you can add the Prefix or Suffix for the sheet names as well. 

4. After specify the settings, then click OK, and the selected data has been split into multiple worksheets of a new workbook by every 5 rows. And the headers are inserted for each new worksheet. See screenshot:

Note: To apply this feature, please download and install Kutools for Excel first.

Related Articles:

  • Split data into multiple worksheets based on column
  • Supposing you have a worksheet with huge rows of data, and now, you need to split the data into multiple worksheets based on the Name column (see following screenshot), and the names are entered randomly. Maybe you can sort them first, and then copy and paste them one by one into other new worksheets. But this will need your patience to copy and paste repeatedly. Today, I will talk about some quick tricks to solve this task.
  • Combine duplicate rows and sum the values in Excel
  • In Excel, it's a common scenario to encounter a dataset with duplicate entries. Often, you might find yourself with a range of data where the key challenge is to efficiently combine these duplicate rows while simultaneously summing up the values in a corresponding column as following screenshot shown. In this context, we'll delve into several practical methods that can help you consolidate duplicate data and aggregate their associated values, enhancing both the clarity and utility of your Excel workbooks.
  • Merge two or more tables into one based on key columns
  • Supposing you have three tables in a workbook, now, you want to merge these tables into one table based on the corresponding key columns to get the result as below screenshot shown. This may be a troublesome task for most of us, but, please don’t worry, this article, I will introduce some methods for solving this problem.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (26)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thanks a lot, you save my life :)
This comment was minimized by the moderator on the site
Very useful thank u so much
This comment was minimized by the moderator on the site
Its awesome!
Rated 5 out of 5
This comment was minimized by the moderator on the site
But header is not retaining in the output. Please guide me if any solution.
This comment was minimized by the moderator on the site
Can we split this same data but by using a code in php?
This comment was minimized by the moderator on the site
U are the savior my brother. Take love from Bangladesh <3 
This comment was minimized by the moderator on the site
Is there a way to keep the header across all worksheet?
For example, row 1 is the column names and row 2 to row 51 are my data. When I split it into 10 worksheets, I hope to have row 1 be the header and row 2-6 be the data.
This comment was minimized by the moderator on the site
this was very helpful. thank you
This comment was minimized by the moderator on the site
I have 20 million data in excel. I need to segregate it with first 2500 rows followed by 250 data so on. How can I do it quickly.
This comment was minimized by the moderator on the site
Minmatar detected :O
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations