How to create new sheets for each row in Excel?
In data management or reporting tasks, you may sometimes need to separate each record from a table into its own worksheet. For example, if you maintain a list of student details in Excel, with each row representing information for a different student, you might wish to generate a separate sheet for every student, each containing their specific data. Alternatively, you may simply want to split every row—regardless of its contents—so that each is placed in a new worksheet on its own. Manually creating and copying data into new sheets can be time-consuming and error-prone, especially if you are working with a large dataset. Automating this process saves significant time and reduces the risk of mistakes. This guide walks you through two practical solutions for creating new sheets for each row in Excel and explains the pros and cons of each method to help you select the best approach for your scenario.
Create new sheets for each row with VBA code
Create new sheets for each row with the Split Data utility of Kutools for Excel
Create new sheets for each row with VBA code
Using VBA (Visual Basic for Applications) is an effective way to automate Excel tasks that are otherwise tedious and repetitive. With VBA, you can quickly create new worksheets for each row in your data, either by grouping rows based on a column (such as a student name), or by creating one sheet for every single row regardless of content. This approach is ideal for users comfortable with the VBA editor and who prefer a highly customizable solution. However, it does require editing and running code, so it may not suit users who do not have experience with VBA or lack permission to run macros in their environment.
1. Press "Alt" + "F11" to open the Microsoft Visual Basic for Applications window, which allows you to access VBA code for automating Excel operations.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module to add a new code module. Then copy and paste the following code into the Module window.
VBA code: create new sheet for each row based on column
Sub parse_data()
'Update by Extendoffice 2018/3/2
Dim xRCount As Long
Dim xSht As Worksheet
Dim xNSht As Worksheet
Dim I As Long
Dim xTRrow As Integer
Dim xCol As New Collection
Dim xTitle As String
Dim xSUpdate As Boolean
Set xSht = ActiveSheet
On Error Resume Next
xRCount = xSht.Cells(xSht.Rows.Count, 1).End(xlUp).Row
xTitle = "A1:C1"
xTRrow = xSht.Range(xTitle).Cells(1).Row
For I = 2 To xRCount
Call xCol.Add(xSht.Cells(I, 1).Text, xSht.Cells(I, 1).Text)
Next
xSUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For I = 1 To xCol.Count
Call xSht.Range(xTitle).AutoFilter(1, CStr(xCol.Item(I)))
Set xNSht = Nothing
Set xNSht = Worksheets(CStr(xCol.Item(I)))
If xNSht Is Nothing Then
Set xNSht = Worksheets.Add(, Sheets(Sheets.Count))
xNSht.Name = CStr(xCol.Item(I))
Else
xNSht.Move , Sheets(Sheets.Count)
End If
xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")
xNSht.Columns.AutoFit
Next
xSht.AutoFilterMode = False
xSht.Activate
Application.ScreenUpdating = xSUpdate
End SubParameter explanation and usage tips:
- A1:C1 is the header range for your data table. Adjust this reference to match your actual data header area as needed.
- The code collects unique values from the specified column (by default, column A). Each unique value gets its own worksheet with the corresponding rows.
- If your table is wider or starts from a different row, update
xTitleand cell references accordingly. - If your worksheet contains formulas that reference other sheets, check references after splitting.
3. Press the F5 key or click Run to execute the code. New worksheets will be created after all existing sheets in the current workbook, each named according to the unique column value chosen. See the screenshot below for a sample result:
If you wish to simply split every row into its own worksheet, regardless of column content, you can use the following alternative VBA code. This eliminates the need to specify a column condition.
VBA code: Directly create new sheet for each row
Sub RowToSheet()
Dim xRow As Long
Dim I As Long
With ActiveSheet
xRow = .Range("A" & Rows.Count).End(xlUp).Row
For I = 1 To xRow
Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & I
.Rows(I).Copy Sheets("Row " & I).Range("A1")
Next I
End With
End SubAfter running this code in the module, each row from the active worksheet—including the header row—will be copied to a newly created worksheet. Note that the header row is also duplicated; if you wish to avoid this, you may need to manually delete or adjust the output sheets afterwards.
Troubleshooting and practical tips:
- If you encounter an error such as "Subscript out of range," ensure that your sheet names do not contain invalid characters or duplicate names.
- Large datasets may result in a large number of sheets, which can slow down Excel or make navigation cumbersome. Consider whether splitting by row is necessary for extremely large tables.
- Always keep a backup of your data before running VBA code to prevent unintentional data loss.
Advantages: Free; highly customizable.
Disadvantages: Requires VBA/macros enabled and some familiarity with VBA editor; potential for code errors with complex or unusual data.
Suitable for: Users needing scripting flexibility, and for one-time or irregular tasks.
Create new sheets for each row with the Split Data utility of Kutools for Excel
If you prefer a straightforward, user-friendly approach without writing code, the Split Data utility of Kutools for Excel is highly recommended. This function helps you split data into new worksheets quickly with just a few clicks, significantly simplifying the worksheet management process. Kutools is especially useful for users who regularly handle large, complex tables or who prefer graphical interfaces over manual coding. Note that Kutools is an Excel add-in and must be installed in advance.
1. Select the table containing the data you want to split into individual worksheets. Then, click the Kutools Plus tab and select Split Data. Refer to the screenshot below for clarity:
2. In the Split Data into Multiple Worksheets dialog box, choose the appropriate settings based on your splitting needs.
A. To create new sheets based on column values:
B. To directly create new sheets for each row:
After executing, a new workbook will be generated containing all the new worksheets—each populated according to the settings you selected.
Creating new sheets for each row based on column values:
Creating new sheet for each row without considering column values:
Precautions and practical tips:
- Before using the Split Data utility, ensure there are no blank rows in your table, as these can result in empty sheets.
- Review sheet naming conventions to avoid duplicate or invalid worksheet names; Kutools will automatically adjust if a name conflict occurs.
- The process can generate a large number of worksheets if your table is very large, so consider this before proceeding.
Advantages: Easy to use with no coding required; suitable for batch processing and frequent tasks.
Disadvantages: Requires Kutools add-in to be installed.
Suitable for: All levels of Excel users, especially those dealing with repetitive data segmentation.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Create new sheets for each row with the Split Data utility of Kutools for Excel
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in