How to Add Sheet with Name from Cell Using Excel VBA

We can save valuable time by using the Excel VBA Macro. Often, we need to add new sheets and name them from a cell manually, but we can do it automatically by applying VBA. In this article, we will show you four methods to add a sheet with the name from a cell using Excel VBA.


Download Practice Workbook


4 Handy Approaches to Add Sheet with Name from Cell Using Excel VBA

This is the snapshot of the dataset from which we will extract the sheet name. We will get the values from the “ID” column for the first three methods to add a single sheet each time. Then for the last method, we will add six sheets by taking the values from the column “Name“.

Excel VBA Add Sheet with Name from Cell 1


1. Adding Single Sheet with Name from Cell

In this first method, we will add a sheet with the name from a cell using Excel VBA. By default, the added sheet will be on the left side of our reference. Moreover, we have named the original sheet “main“.

Excel VBA Add Sheet with Name from Cell 2

Steps:

  • First, we bring up the VBA Module window, where we type our codes.
  • To do this, from the Developer tab → select Visual Basic. Alternatively, you can press ALT+F11 to bring up this too.

Excel VBA Add Sheet with Name from Cell 3

  • So, the VBA window will pop up.
  • Next, from the Insert tab, select Module.
  • Here, we insert VBA code into Excel.

Excel VBA Add Sheet with Name from Cell 4

  • After that, type the following code inside the VBA Module window.
Option Explicit

Sub Add_New_Sheet()
    
    Worksheets("main").Activate
    Sheets.Add.Name = Cells(5, 2).Value

End Sub

Excel VBA Add Sheet with Name from Cell 5

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Add_New_Sheet.
  • Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
  • Next, we are using the Add.Name method to name the newly created sheet. Moreover, we have used the Cells property to refer to a cell value. Cells(5,2) signify row 5 and column 2, which means cell B5.
  • Thus, this code works.
  • Afterward, Save the Module.
  • Then, put the cursor inside the Sub procedure and press Run.

Excel VBA Add Sheet with Name from Cell 6

  • So, our code will execute and it will add a sheet named “E-101”, which is taken from the cell value B5.

Excel VBA Add Sheet with Name from Cell 7

Read More: How to Create New Sheet from Template Using Macro in Excel


2. Applying Excel VBA to Add Sheet Name After Specific Sheet

We will add a sheet after a specific sheet using Excel VBA in this method.

2.1. After Specific Sheet

In this section, we will add a sheet after the sheet named “E-101“.

Steps:

Option Explicit

Sub Add_New_Sheet_After_Specific_Sheet()

    Worksheets("main").Activate
    Sheets.Add(After:=Sheets("E-101")).Name = Range("B7").Value

End Sub

Excel VBA Add Sheet with Name from Cell 8

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Add_New_Sheet_After_Specific_Sheet.
  • Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
  • Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created after the “E-101” sheet that we created in the last method. Moreover, we have used the Range property to refer to the cell value from cell B7, which is “E-103”.
  • Thus, this code works.
  • Then, as shown in method 1, we Save and Run this Module.
  • Thus, this code will add a sheet named “E-103” after the “E-101” sheet.

Excel VBA Add Sheet with Name from Cell 9

2.2. After Last Sheet

In this section, we will add a sheet after the last sheet of the workbook.

Steps:

Option Explicit

Sub Sheet_Start_Workbook()

    Worksheets("main").Activate
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Range("B10").Value

End Sub

Excel VBA Add Sheet with Name from Cell 10

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Add_New_Sheet_After_Specific_Sheet.
  • Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
  • Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created after the last sheet from the workbook. This last sheet number will be obtained from the “Sheets.Count” property. Moreover, we have used the Range property to refer to the cell value from cell B10, which is “E-106”.
  • Thus, this code works by adding a sheet with the name value from a cell.
  • Then, as shown in method 1, we Save and Run this Module.
  • Thus, this code will add a sheet after the last sheet.

Excel VBA Add Sheet with Name from Cell 11

Read More: Excel VBA: Add Sheet After Last (3 Ideal Examples)


3. Adding Sheet Before Specific Sheet with Name from Cell

In this method, we will add a sheet with a name from a cell before a specific sheet in Excel using a VBA Macro.

3.1. Before Specific Sheet

We will insert a sheet with the name from a cell before a specific sheet by applying Excel VBA.

Steps:

Option Explicit

Sub Add_New_Sheet_Before_Specific_Sheet()

    Worksheets("main").Activate
    Sheets.Add(Before:=Sheets("E-103")).Name = Range("B6").Value

End Sub

Excel VBA Add Sheet with Name from Cell 12

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Add_New_Sheet_Before_Specific_Sheet.
  • Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
  • Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created before the sheet called “E-103” from the workbook. Moreover, we have used the Range property to refer to the cell value from cell B6, which is “E-102”.
  • Thus, this code works by adding a sheet with the name value from a cell.
  • Then, as shown in method 1, we Save and Run this Module.
  • Thus, this code will add a sheet “E-102” before the “E-103” sheet.

Add New Sheet

3.2. Start of Workbook

We will insert a sheet before the first sheet in Excel using VBA in this section.

Steps:

Option Explicit

Sub Sheet_Start_Workbook()

    Worksheets("main").Activate
    Sheets.Add(Before:=Sheets(1)).Name = Cells(8, 2).Value

End Sub

VBA Code Module

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Sheet_Start_Workbook.
  • Then, we activate the “main” sheet. This will make sure the code will run if we are on another sheet.
  • Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created before the first sheet from the workbook. This action will be executed before the first sheet, as denoted by the “Sheets(1)” property. Moreover, we have used the Cells property to refer to a cell value. Cells(8,2) signify row 8 and column 2, which means cell B8.
  • Thus, this code works by adding a sheet with the name value from a cell.
  • Then, as shown in method 1, we Save and Run this Module.
  • Thus, this code will add a sheet before the first sheet.

Add New Sheet 2

Read More: Excel Macro: Create New Sheet and Rename (3 Ideal Examples)


4. Inserting Multiple Sheets with Name from Cell Using Excel VBA

For the last method, we will add multiple sheets in the workbook with the name from a range of cells using Excel VBA. Moreover, we will ask the user for input on the range to take the name from. Lastly, we will also be using a For Each Next loop to go through all the cells.

Steps:

Option Explicit

Sub Add_Sheets_from_Cell_Value()

    Dim xRange As Range
    Dim qq As Range

    Set xRange = Application.InputBox("Select Cell Range" _
    & "to Create Sheets", "ExcelDemy", Type:=8)
    
    Application.ScreenUpdating = False
    
    For Each qq In xRange
        On Error Resume Next ' This will ignore any error
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = qq.Value

    Next qq
    
    Application.ScreenUpdating = True
End Sub

VBA Code Module 2

VBA Code Breakdown

  • Firstly, we are calling the Sub procedure as Add_Sheets_from_Cell_Value.
  • Then, we define the variable types.
  • Afterward, we use an InputBox to get the range data from the user.
  • Then, we use a For Each Next loop to go through the selected cell range one-by-one.
  • Next, we are using the Add.Name method to name the newly created sheet. This sheet will be created after the last sheet from the workbook. This last sheet will be found out by the “Sheets.Count” property. Moreover, we have added the “On Error Resume Next” statement before the loop. This will ignore any errors and continue the looping process.
  • Thus, this code works by adding a sheet with the name value from a cell.
  • Then, as shown in method 1, we Save and Run this Module.
  • Afterward, this code will ask the user to input the range.
  • Then, select the cell range C5:C10 and press OK.

Input Box

  • As a result, it will create six new sheets in the workbook. Additionally, we have set the code to ignore any errors. So, if your cell range has duplicate values, then this code will not stop; rather, for that duplicate value, it will give a generic name (for example “sheet16”) to the newly created sheet.

Add Multiple Sheets

Read More: Excel VBA to Add Sheet with Variable Name (5 Ideal Examples)


Things to Remember

  • Firstly, the worksheet name cannot be blank.
  • Secondly, sheet names can contain a maximum of 31 characters.
  • Thirdly, there should not be any slashes, question marks, star marks (multiple signs), third brackets, or colons.
  • Next, the sheet name should begin or end with an apostrophe.
  • Lastly, you should not name the sheet with reserved words such as “History” in Excel.

Conclusion

We have shown you four quick ways to add a sheet with a name from a cell in Excel using VBA. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo