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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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.
Actually, the above method is complicate and hard to understand. In this section, we introduce you the Split Data utility of Kutools for Excel.
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:
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
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agoThis 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.· 4 months agoGreat 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?
To post as a guest, your comment is unpublished.· 6 months agoHi, 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:
Dim xRow As Long
Dim I As Long
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")
To post as a guest, your comment is unpublished.· 9 months agoHello, 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.· 1 years agoHello I tried to use your code but I get an error
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