Split data into multiple worksheets by rows count in Excel
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
xRow.Resize(resizeCount).Copy
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
Else
xWs.Name = xRow.Row & " - " & xIER
End If
Application.ActiveSheet.Range("A1").PasteSpecial
Set xNTRg = Application.ActiveSheet.Range("A1")
xTRg.Copy
xNTRg.Insert
Set xRow = xRow.Offset(SplitRow)
Next
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.
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:
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
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!