How to split a large table into multiple small tables in Excel?
If you have a large worksheet which contains multiple columns and hundreds or thousands of rows data, now, you want to split this large table into multiple small tables based on the column value or number of rows to get the following results. How could you deal with this task in Excel?
| Main Table | Split table into multiple tables by column value | Split table into multiple tables by rows count | |
 |
 |
 |
 |
Split a large table into multiple tables based on column value with VBA code
Split a large table into multiple tables based on the specific number of rows with VBA code
Split a large table into multiple tables based on column value with VBA code
To split this large table into multiple tables based on a specific column value, the following VBA code can do you a favor. Please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Split a large table into multiple tables by key column:
Sub Splitdatabycol()
'by Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""
Else
Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
Sheets(myarr(i) & "").Paste Destination:=Sheets(myarr(i) & "").Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub
3. After pasting the code, press F5 key to run this code, and a prompt box is popped out, please select the header row from your data, see screenshot:
4. Then, click OK, and another dialog box is popped out, please select the column data that you want to split the table based on, see screenshot:
5. Click OK, this large table has been split into multiple worksheets by the column value which are located after the master sheet. And the new worksheets are named with the column value. See screenshot:
Split a large table into multiple tables based on the specific number of rows with VBA code
If you need to split the table into multiple tables based on the number of rows, the following VBA code can help you.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Split a large table into multiple tables by number of rows:
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. And then, 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 master table has been split into multiple worksheets based on the number of rows as below screenshot shown:
Split a large table into multiple tables based on column value or number of rows with an amazing feature
May be the above codes are difficult for most of users, here, I will introduce an amazing feature-Split Data of Kutools for Excel. With this utility, you can split a large table into multiple tables by key column or number of rows quickly and easily.
Tips:To apply this Split Data feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.
After installing Kutools for Excel, please do as this:
1. Select the data range that you want to split, and then, click Kutools Plus > Split Data, see screenshot:
2. In the Split Data into Multiple Worksheets dialog box, specify the settings to your need:
(1.) Select Specific column or Fixed rows from the Split based on section as you need;
(2.) Specify the new worksheets name from the Rules drop down list, you can add the Prefix or Suffix to the sheet names as well.
3. Then, click Ok button, and now, the large table has been split into multiple small tables in a new workbook. See screenshots:
| Split table into multiple tables by column value | Split table into multiple tables by rows count |
 |
 |
Click to Download Kutools for Excel and free trial Now!
More relative articles:
- Split A Workbook To Separate Excel Files In Excel
- You may need to split a large workbook to separate Excel files with saving each worksheet of the workbook as an individual Excel file. For example, you can split a workbook into multiple individual Excel files and then deliver each file to different person to handle it. By doing so, you can get certain persons handle specific data, and keep your data safe. This article will introduce ways to split a large workbook to separate Excel files based on each worksheet.
- Split Full Name To First And Last Name In Excel
- Supposing you have a name roster as the first screen shot shows in single column below, and you need to split the full name to the first name column， middle name column and last name column as the following screenshot shown. Here are some tricky methods to help you solve this problem.
- Split A Long Column Into Multiple Columns In Excel
- If you have a long column data in Excel which will be troublesome when viewing them. But now if you can split such long list into multiple columns in Excel, it will make the viewing conveniently.
- Split Word Or Number Into Separate Cells In Excel
- If you have a list of numbers or words in a worksheet, and now you need to split the cell contents into letters on different cells as following screenshot shown, how can you deal with this job in Excel?
- Split A Long List Into Equal Groups In Excel
- If you have a long list of data needed to be split into multiple equal groups as following screenshot shown, how could you deal with this task quickly and easily in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- 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!
