How to Create New Sheets for Each Row in Excel (4 Quick Ways)

Sometimes, we need to create new sheets in our Excel workbook for each corresponding row of any dataset. We can do this task in several ways. In this article, we are going to demonstrate to you four possible methods to create new sheets for each row in Excel. If you are curious about it, download our practice workbook and follow us.

How to Create New Sheets for Each Row in Excel: 4 Easy Methods

To demonstrate the approaches, we consider a dataset of five employees of any organization and their income for the months of January and February. Besides that, we also show the total income for both months. So, we can claim that our dataset is in the range of cells B5:E9. We are going to create individual new sheets for those five rows.

1. Using Excel Pivot Table

In this method, we will use the Excel Pivot Table feature to create new sheets for each row. The steps of this process are given below:

📌 Steps:

• First of all, select the range of cells B4:B9.
• Now, in the Insert tab, select the drop-down arrow of the PivotTable > From Table/ Range option from the Tables group.

• As a result, a small dialog box called PivotTable from table or range will appear.
• Then, change the output display location of the pivot table from New Worksheet to Existing Worksheet and select the cell in the Location field. For our dataset, we selected cell G4 to show the pivot table.
• Finally, click OK.

• You will see the pivot table will be created, and a side window called PivotTable Field will appear.

• You may notice the Name range in this window.
• Now, drag the Name range with your mouse and place it inside the Filter field. The pivot table in the worksheet will also show you the Name range.

• After that, in the PivotTable Analyze tab, click the drop-down arrow of the Options and select the Show Report Filter Pages option from the PivotTable group.

• Another small dialog box called Show Report Filter Pages will appear.
• Select the Name option and click OK.

• You will see five new sheets will be created in the Sheet Name Bar with the name of each row.

Thus, we can say that our method worked perfectly, and we were able to create new sheets for each row in Excel.

2. Using VBA Application.Proper Method

Writing a VBA code can also help us to create new sheets for each row. For showing this method, we are using the same datasheet which we have already used. The steps of this process are given as follows:

📌 Steps:

• To start the approach, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• A dialog box will appear.
• Now, in the Insert tab on that box, click on Module.

• Then, write down the following visual code in that empty editor box.

Sub Rows_to_New_Sheet()
Dim A, W_S As Worksheet, LastRow
On Error Resume Next
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
For Each A In Range("B4:B" & LastRow)
    If A.Value <> "" Then
        Set W_S = Worksheets(A.Value)
        If W_S Is Nothing Then
            Sheets.Add after:=Sheets(Sheets.Count)
            ActiveSheet.Name = Application.Proper(A.Value)
        End If
    End If
Next A
End Sub

• Close the Editor tab.
• After that, in the Developer tab, select the Macros option from the Code group.

• A new dialog box called Macro will appear. Select Rows_to_New_Sheet.
• Click on the Run button to run this code.

• You will notice six new sheets will be created in the Sheet Name Bar with the name of each row. This code will create a new sheet for the table heading row also.

So, we can say that our VBA code worked perfectly, and we were able to create new sheets for each row in Excel.

3. Using Input Box

Besides the VBA code, we will insert an input box to create new sheets for each row. In that input box, we have to mention the row range for which we want to create new sheets. The steps of this approach are given below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

• As a result, a dialog box will appear.
• After that, in the Insert tab on that box, click Module.

• Now, write down the following visual code in that empty editor box.

Sub Create_New_Sheet()
Dim Range As Range
Dim Cell As Range
On Error GoTo Errorhandling
Set Range = Application.InputBox(Prompt:="Select Cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
For Each Cell In Range
    If Cell <> "" Then
        Sheets.Add.Name = Cell
    End If
Next Cell
Errorhandling:
End Sub

• Next, close the Editor tab.
• Then, in the Developer tab, select the Macros option from the Code group.

• A small dialog box called Macro will appear.
• Select Create_New_Sheet and click on the Run button to run this code.

• As a result, a small input dialog box called Create Sheet will appear.
• Now, select the range of cells for which we want to create new sheets. We select the range of cells B5:B9.
• Finally, click OK.

• You will see five new sheets will be created in the Sheet Name Bar with the name of each row. Moreover, every sheet is entitled to the cell entity.

Hence, we can say that our VBA code worked effectively, and we were able to create new sheets for each row in Excel.

Read More: How to Insert Sheet from Another File in Excel

4. Defining Row Number

In this VBA code, we are going to define the row number from where we want to create new sheets for each row. The procedure is explained below step-by-step:

📌 Steps:

• At first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• Afterward, in the Insert tab on that box, click on the Module option.

• After that, write down the following visual code in that empty editor box.

Sub Row_To_Sheet()
    Dim X_Row As Long
    Dim A As Long
    With ActiveSheet
        X_Row = .Range("B5").End(xlDown).Row
        For A = 5 To X_Row
            Worksheets.Add(, Sheets(Sheets.Count)).Name = "Row " & A
        Next A
    End With
End Sub

• Close the Editor tab.
• Now, in the Developer tab, select the Macros option from the Code group.

• As a result, a new dialog box called Macro will appear.
• Then, select Row_To_Sheet and click on the Run button.

• You will notice five new sheets will be created in the Sheet Name Bar with the name of the Excel row number.

Finally, we can say that our VBA code worked successfully, and we were able to create new sheets for each row in Excel.

Read More: How to Create Multiple Sheets in Excel with Different Names

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to create new sheets for each row in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Related Articles

• How to Create Multiple Sheets with Same Format in Excel
• Understanding Excel Spreadsheets
• How to Create Multiple Worksheets from a List of Cell Values
• How to Create Multiple Sheets in Excel at Once

<< Go Back to  Insert Sheet | Worksheets | Learn Excel