How to Add Sheet with Name from Cell Using Excel VBA

In this article, we will use the following dataset to demonstrate four methods to add a sheet with a name derived from a cell using Excel VBA. In the first three methods, we will add a single sheet with the sheet name extracted from the “ID” column in each case. 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


Method 1 – Adding a Single Sheet with Name from a Cell

By default in Excel, a new sheet will be added on the left side of our reference sheet. We have named this 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.

  • From the Developer tab → select Visual Basic. Alternatively, press ALT+F11.

Excel VBA Add Sheet with Name from Cell 3

The VBA window will pop up.

  • From the Insert tab, select Module.

Excel VBA Add Sheet with Name from Cell 4

  • In the module window that opens, enter the following code in 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

  • We name the Sub procedure as Add_New_Sheet.
  • We activate the “main” sheet to ensure that the code will run if we are on another sheet.
  • We use the Add.Name method to name the newly created sheet.
  • We use the Cells property to refer to a cell value. Cells(5,2) signifies the cell at row 5 and column 2, namely cell B5.
  • Save the Module.
  • Put the cursor inside the Sub procedure and press the green Run button.

Excel VBA Add Sheet with Name from Cell 6

Our code will execute and add a sheet named “E-101”, the value in cell B5.

Excel VBA Add Sheet with Name from Cell 7

Read More: Excel Macro: Create New Sheet and Rename


Method 2 – Adding a Sheet After a Specific Sheet

We can also add a sheet after a specific sheet using Excel VBA.

2.1 – After a Specific Sheet

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

  • We name the Sub procedure Add_New_Sheet_After_Specific_Sheet.
  • We activate the “main” sheet to ensure the code will run if we are on another sheet.
  • We use 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.
  • We use the Range property to refer to the value in cell B7, which is “E-103”.

The code will add a sheet named “E-103” after sheet “E-101”.

Excel VBA Add Sheet with Name from Cell 9

Read More: How to Add Sheet After Current One with Excel VBA


2.2 – After the Last Sheet

Now let’s 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

  • We name the Sub procedure Add_New_Sheet_After_Specific_Sheet.
  • We activate the “main” sheet to ensure the code will run if we are on another sheet.
  • We use the Add.Name method to name the newly created sheet. This sheet will be created after the last sheet in the workbook. This last sheet number is obtained from the “Sheets.Count” property.
  • We use the Range property to refer to the value in cell B10, which is “E-106”.

The code adds a sheet after the last sheet.

Excel VBA Add Sheet with Name from Cell 11


Method 3 – Adding a Sheet Before a Specific Sheet

3.1 – Before a Specific Sheet

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

  • We name the Sub procedure Add_New_Sheet_Before_Specific_Sheet.
  • We activate the “main” sheet to ensure the code will run if we are on another sheet.
  • We use the Add.Name method to name the newly created sheet. This sheet will be created before the sheet called “E-103”.
  • We use the Range property to refer to the value in cell B6, which is “E-102”.

The code will add a sheet “E-102” before sheet “E-103”.

Add New Sheet


3.2 – At the Start of a Workbook

We can insert a sheet right at the beginning, before the first sheet.

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

  • We name the Sub procedure as Sheet_Start_Workbook.
  • We activate the “main” sheet to ensure the code will run if we are on another sheet.
  • We use 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.
  • We use the Cells property to refer to a cell value. Cells(8,2) signify row 8 and column 2, which means cell B8.

Add New Sheet 2


Method 4 – Inserting Multiple Sheets with Name from Cell

For the last method, we will add multiple sheets to the workbook with names from a range of cells using Excel VBA. We will ask the user to input the range from which to take the names. We’ll then use 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

  • We name the Sub procedure as Add_Sheets_from_Cell_Value.
  • We define the variable types.
  • We use an InputBox to get the range data from the user.
  • We use a For Next loop to go through the selected cell range cell-by-cell.
  • We use the Add.Name method to name the newly created sheet. This sheet will be created after the last sheet in the workbook. This last sheet is determined by the “Sheets.Count” property.
  • We add the “On Error Resume Next” statement before the loop. This will ignore any errors and continue the looping process.

A Message Box opens asking to input the range.

  • Select the cell range C5:C10 and press OK.

Input Box

As a result, six new sheets are created in the workbook. Additionally, we set the code to ignore any errors, so if your cell range has duplicate values, the code will not terminate; 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


Things to Remember

  • A worksheet name cannot be blank.
  • Sheet names can contain a maximum of 31 characters.
  • There should not be any slashes, question marks, asterisks, third brackets, or colons.
  • The sheet name should not begin or end with an apostrophe.
  • Don’t name the sheet with reserved words such as “History”.

Download Practice Workbook


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo