How to split a large table into multiple small tables in Excel?

If you have a large Excel table with multiple columns and hundreds or thousands of rows, you may want to split it into smaller, more manageable tables. For instance, you might need to separate the data based on specific column values or divide it into chunks of a fixed number of rows. This tutorial provides step-by-step methods to accomplish this task efficiently.

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 or number of rows with Kutools for Excel

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". The large table has now been split into multiple worksheets based on column values, with the new worksheets 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 Kutools for Excel

The above codes might be difficult for most 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.

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

More relative articles: