How To Automatically Create A New Sheet In Excel

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

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.

Before applying Kutools for Excel, please download and install it firstly.

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:

If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.

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

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
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 without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... 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...
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...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

kte tab 201905

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!

officetab bottom