How to Add a Named Sheet in Excel VBA (6 Methods)

VBA Code to Add a Sheet with a Name

To add a sheet with a specific name in Excel using VBA, we’ll utilize the Sheets object. Below is the fundamental VBA code snippet to achieve this:

Here’s what each parameter means:

• Before: An optional parameter. It adds a new sheet before a specific sheet.
• After: Also optional. It adds a new sheet after a specific sheet.
• Count: Another optional parameter. It indicates the number of sheets to add.
• Type: Optional as well. It specifies the type of sheet. The default value is xlWorksheet.

Example Scenario

Let’s say we have a worksheet named “Sales Report.” It contains daily sales data for a cafeteria, with columns B, C, and D representing Sales Reps, Items, and Quantity, respectively.

Method 1 – Adding a Sheet with Name in Excel VBA

In this method, we’ll add a sheet with a user-defined name. Follow these steps:

• Go to the “Developer” tab.
• Select “Visual Basic” in the “Code” group.
  • Alternatively, press ALT + F11.

Note: Ensure you press the ALT key to the left of the SPACEBAR; other ALT keys won’t work. 

• The Microsoft Visual Basic for Applications window will open.
• Move to the Insert tab and select Module.

• Write the following code in the Module:

Sub Add_Sheet_with_Name()
Dim sheet_name As String
Dim sheet As Object
On Error Resume Next
sheet_name = InputBox("Please enter sheet name ", _
"Exceldemy")
If sheet_name = "" Then Exit Sub
Sheets.Add.Name = sheet_name
End Sub

• Click the Run (⏵) icon or press F5 on your keyboard.

• An input box appears; enter the desired sheet name (e.g., “Profit”) and click OK.

• The code executes, adding a sheet named “Profit.”

Note: By default, the sheet is placed before the active sheet.

Read More: Excel VBA to Add Sheet with Variable Name

Method 2 – Adding a Sheet Before a Specific Sheet

In this method, we’ll create a new sheet with a custom name before a specific existing sheet. Follow these steps:

• Open the Visual Basic for Applications (VBA) editor by going to the Developer tab and selecting Visual Basic in the Code group. Alternatively, press ALT + F11.
• In the VBA editor, create a new module (if not already done).
• Write the following code in the module:

Sub Add_Sheet_Before_Specific_Sheet()
Worksheets("Sales Report").Activate
Sheets.Add(Before:=Sheets("Profit")).Name = "Balance Sheet"
End Sub

• Run the code (click the Run icon or press F5).
• The result: A new sheet named “Balance Sheet” will be added before the sheet named “Profit.”

Read More: Excel VBA to Add Sheet If It Does Not Exist

Method 3 – Adding a Sheet After a Specific Sheet

In this method, we’ll add a sheet after an existing sheet (specifically, after the sheet named “Profit”). Follow these steps:

• Open the VBA editor.
• Create a new module (if needed).
• Write the following code:

Sub Add_Sheet_After_Specific_Sheet()
Worksheets("Profit").Activate
Sheets.Add(After:=ActiveSheet).Name = "Warehouse"
End Sub

• Run the code.
• Result: A new sheet named “Warehouse” will be added after the sheet named “Profit.”

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

Method 4 – Inserting a Sheet at the Start of the Workbook

To insert a new sheet at the beginning of the workbook, follow these steps:

• Open the VBA editor.
• Create a new module.
• Write the following code:

• Run the code.
• Outcome: A new sheet named “Company Profile” will be added at the start of the workbook.

Read More: Excel Macro: Create New Sheet and Rename

Method 5 – Adding a Sheet at the End of the Workbook

To add a sheet after the last sheet in the workbook, follow these steps:

• Open the VBA editor.
• Create a new module.
• Write the following code:

Sub Sheet_End_Workbook()
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Income Statement"
End Sub

• Run the code.
• Result: A new sheet named “Income Statement” will be added at the end of the workbook.

Method 6 – Adding Multiple Sheets Using Cell Values in Excel VBA

In this method, we’ll create multiple sheets in the workbook, naming them based on values from a specified range of cells. Additionally, we’ll prompt the user to enter the cell range for the sheet names. Let’s walk through the steps:

• Open the Visual Basic for Applications (VBA) editor by following the steps mentioned in Method 1.
• Copy and paste the following code into a new module:

Sub Add_Multiple_Sheets_Using_Cell_Value()
Dim rng As Range
Dim cc As Range
Set rng = Application.InputBox("Select Cell Range" _
& " to Insert Sheets", "ExcelDemy", Type:=8)
Application.ScreenUpdating = False
Worksheets("Sales Report").Activate
For Each cc In rng
Sheets.Add(After:=ActiveSheet).Name = cc.Value
Next cc
Application.ScreenUpdating = True
End Sub

• Run the code (click the Run icon or press F5).
• Instantly, the Exceldemy input box opens.
• Insert the cells in the B5:B9 range in the box.
• Lastly, click OK.

• Result: Five new sheets will be inserted, each named after the cell values in the specified range (e.g., B5:B9), all placed after the “Sales Report” sheet.

Read More: How to Add Sheet with Name from Cell Using Excel VBA

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• Excel Macro to Create New Sheet and Copy Data
  
• How to Create New Sheet from Template Using Macro in Excel