How to Create Multiple Worksheets from a List of Cell Values

On many occasions, we might be in need of creating multiple worksheets at a single time. On top of that, we need to rename them in a certain order. We can list those names and create those worksheets. If you are curious to know how you can create multiple worksheets from a list of cell values, then this article may come in handy for you. In this article, we discuss how you can create multiple worksheets from a list of cell values with an elaborate explanation.


Download Practice Workbook

Download this practice workbook below.


5 Easy Ways to Create Multiple Worksheets from a List of Cell Values

To demonstrate the approaches, we consider a dataset of five resident of a fictional neighborhood and their name with their profession. Besides it, we also show the name of their house name, to which they belong. 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 in the list as cell values.


1. Using Excel Pivot Table

This method will use the Excel Pivot Table feature to create multiple new worksheets from a List of Cell Values. The steps of this process are given below:

Steps

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

Using Excel Pivot Table to Create Multiple Worksheets from a List of Cell Values

  • 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 F4 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.

Using Excel Pivot Table to Create Multiple Worksheets from a List of Cell Values

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

Using Excel Pivot Table to Create Multiple Worksheets from a List of Cell Values

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


2. Applying VBA Trim Function

Using a VBA can drastically reduce the time required to create multiple worksheets from a list of cell values in Excel. In this procedure, we are going to use a VBA code containing the Trim function which will help us achieve this.

Steps

  • To initiate a VBA, go to the Developer tab, then click on Visual Basic from the Code group.

Using VBA Trim Function to Create Multiple Worksheets from a List of Cell Values

  • Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
  • Next, in the Module editor window, type the following code:
Sub Trim_Function()
Dim Present_Sheet As Worksheet
Dim Cell_Range As Range
Dim x As Range
Set Present_Sheet = ActiveSheet
Set Cell_Range = Selection.Cells
Application.ScreenUpdating = False
For Each x In Cell_Range
sName = Trim(x.Text)
If Len(sName) > 0 Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = sName
End If
Next x
Present_Sheet.Activate
Application.ScreenUpdating = True
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros (double click).

Using VBA Trim Function to Create Multiple Worksheets from a List of Cell Values

  • After clicking View Macros, select the macros that you created just now. The name here is Trim_Function. Then click Run.

  • You will notice that 5 new sheets will be created in the Sheet Name Bar with the name of each row.

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

3. Utilizing VBA Debug Print

In this procedure, we will use a VBA code where we will use the VBA Debug Print property to create multiple worksheets from a given list of cell values in Excel.

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.

Utilizing VBA Debug Print to Create Multiple Worksheets from a List of Cell Values

  • Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
  • Next, in the Module editor window, type the following code:
Sub Debug_Function()
Dim yRg As Excel.Range
Dim wSt As Excel.Worksheet
Dim wBo As Excel.Woarkbook
Set wSt = ActiveSheet
Set wBo = ActiveWorkbook
Application.ScreenUpdating = False
For Each yRg In wSt.Range("B5:B9")
With wBo
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = yRg.Value
If Err.Number = 1004 Then
Debug.Print yRg.Value & " sheet name already exist"
End If
On Error GoTo 0
End With
Next yRg
Application.ScreenUpdating = True
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

Utilizing VBA Debug Print to Create Multiple Worksheets from a List of Cell Values

  • After clicking View Macros, select the macros that you created just now. The name here is Debug_Function. Then click Run.

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


4. Using VBA Application.Proper Method

Writing a VBA code can also help us to create multiple worksheets from a List of Cell Values. 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

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

  • Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
  • Next, in the Module editor window, type the following code:
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("B5: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

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

  • After clicking View Macros, select the macros that you created just now. The name here is Rows_to_New_Sheet. Then click Run.

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

Using VBA Application.Proper Method to Create Multiple Worksheets from a List of Cell Values


5. Utilizing Input Box

Besides the VBA code, we will insert an input box to create multiple worksheets from a List of Cell Values. In that input box, we have to mention the cell range of the list for which we want to create multiple worksheets. 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.

Using VBA Application.Proper Method to Create Multiple Worksheets from a List of Cell Values

  • Then there will be a new dialog box, in that dialog box, click on the Insert > Module.
  • Next, in the Module editor window, type the following code:
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 Multiple Worksheets", _
Default:=Selection.Address, Type:=8)
For Each Cell In Range
If Cell <> "" Then
Sheets.Add.Name = Cell
End If
Next Cell
Errorhandling:
End Sub

  • Then close the Module window.
  • After that, go to View tab > Macros.
  • Then click on View Macros.

Using Input Box to Create Multiple Worksheets from a List of Cell Values

  • After clicking View Macros, select the macros that you created just now. The name here is Create_New_Sheet. Then click Run.

  • After clicking Run, you will notice that there is a dialog box, asking for the range of cells of our intended list.
  • Select the range of cell B5:B9 and click OK.

After clicking View Macros, select the macros that you created just now. The name here is Create_New_Sheet. Then click Run.

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

After clicking View Macros, select the macros that you created just now. The name here is Create_New_Sheet. Then click Run.

Read More: How to Insert Sheet from Another File in Excel (3 Easy Ways)


Conclusion

To sum it up, the issue of how we can create multiple worksheets from a list of cell values is answered here by 5 different examples. The VBA Macro method requires prior VBA-related knowledge to understand from scratch.

For this problem, a macro-enabled workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo