Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to create new sheets for each row in Excel?

Supposing you have a score table with all student’s name in column A. Now you want to create new sheets based on these names in column A, and make per sheet contains one unique student’s data. Or just create new sheet for each row on the table without considering the name in column A. In this article, you will get methods to achieve it.

Create new sheets for each row with VBA code

Create new sheets for each row with the Split Data utility of Kutools for Excel


Easily create new sheets for each row or based on specific column value in Excel:

If you have Kutools for Excel, its utility – Split Data can help you quickly create new sheets based on specific coumn values or create new sheets for each row in Excel. See screenshot:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Create new sheets for each row with VBA code


With the following codes, you can create new sheet based on column values, or just create new sheets for each row in Excel.

1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. And then 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 Sub

Note: A1:C1 is the title range of your table. You can change it based on your needs.

3. Press F5 key to run the code, then new worksheets are created after all worksheets of the current workbook as below screenshot:

If you want to directly create new sheets for each row without considering the column value, you can use the following code.

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 Sub

After running the code, each row in active worksheet will be placed in a new worksheet.

Note: The heading row will also be placed in a new sheet with this VBA code.


Create new sheets for each row with the Split Data utility of Kutools for Excel

Actually, the above method is complicate and hard to understand. In this section, we introduce you the Split Data utility of Kutools for Excel.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

1. Select the table you need to use to create new sheets, and then click Kutools Plus> Spit Data. See screenshot:

2. In the Split Data into Multiple Worksheets dialog box, please do as follows.

A. For creating new sheets based on column value:

1). Please select the Specific column option, and specify a column that you want to split data based on in the drop-down list;

2). If you want to name the worksheets with column values, please select Values of Column in the Rules drop-down list;

3). Click the OK button. See screenshot:

B. For directly creating new sheets for each row:

1). Select Fixed rows option, enter number 1 into the box;

2). Select Row Numbers from the Rules drop-down list;

3). Click the OK button. See screenshot:

a new workbook is created with all new sheets inside. See screenshots below.

Creating new sheets for each row based on column value:

Creating new sheet for each row without considering column value:

Create new sheets for each row with the Split Data utility of Kutools for Excel

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    ComplianceHound · 3 months ago
    This is great code. Many thanks to brain-boxes at OfficeExtend !! Is there anyway this code could be slightly adapted to to create separate sheets for each *column* instead of row? I've attached a picture of what I'm trying to achieve. Is this possible? Kind regards.
  • To post as a guest, your comment is unpublished.
    Sam · 4 months ago
    Great code, but could I get some help if I my data is on column G instead of column A? what do I need to change to have the Column G data in different tab?

    Thanks
  • To post as a guest, your comment is unpublished.
    Michele · 6 months ago
    Hi, is there a way to keep the heading row on each new worksheet? (circled in red on my attachment)

    The code takes all the rows from my master worksheet and transfers them into new worksheets, which is great. But I want to keep my "master" header value (circled in red) at the top of each new worksheet. Thanks!



    I'm referring to this code from above:

    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 Sub
  • To post as a guest, your comment is unpublished.
    BK TAN · 9 months ago
    Hello, I think there is something useful here for my situation, but I'm able to to do VBA or script, hope you can help.
    I have a template w/ many cells to fill with data, and there will be a search key (non unique) that I would like to enter into the template. Based on the search key, the data is searched and corresponding data on the matched key is fetched and filled into the template. The filled template is saved into a new worksheet. There maybe more than 1 match entries. I need the script to continue search down the list, until all matches are picked, and the certain number of new worksheets created.
  • To post as a guest, your comment is unpublished.
    Beatriz · 1 years ago
    Hello I tried to use your code but I get an error
    Run-timeerror '1004':
    Application-defined or object-defined error
    I have no knowledge of VBA (or any technology for that matter) but if a press debug it highlights line 11 xRCount=xSht.Cells(xSht.Rows.Count,1). End(xIUp).Row
    I am working with a large file that has 127 columns and 337 rows (rows will vary columns won't) and it is a list with I'd numbers and their details.
    I did change the range as you noted but still doesn't work I'm using Excel 2010 could you please tell me how to make it work if possible
    Thank you
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Beatriz,
      The code is updated with the problem solving. Please try it again. Thank you for your comment.