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

Get FREE Advanced Excel Exercises with Solutions!

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.

Watch Video â€“ Create New Sheets for Each Row in Excel

## 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
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", _
For Each Cell In Range
If Cell <> "" Then
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.

### 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.

## 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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF