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

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:

Sheets.Add ([Before], [After], [Count], [Type])

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.

Excel VBA Add Sheet with Name


Method 1 – Adding a Sheet with a Name

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.

Excel VBA Add Sheet with Name

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.

Excel VBA Add Sheet with Name

  • 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

Code Breakdown

  • We define a Sub procedure called Add_Sheet_with_Name.
  • Variable types are specified.
  • We use On Error Resume Next to ignore any errors.
  • An InputBox prompts the user to enter the desired sheet name, and we store it in the sheet_name variable.
  • If the sheet name is empty, the code exits.
  • We use the Add.Name method to name the newly created sheet with the user-provided name.

Excel VBA Add Sheet with Name

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

Excel VBA Add Sheet with Name

  • An input box appears. Enter the desired sheet name (e.g., Profit) and click OK.

Excel VBA Add Sheet with Name

  • The code executes, adding a sheet named Profit.

Excel VBA Add Sheet with Name

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

Code Breakdown

  • We define a Sub procedure called Add_Sheet_Before_Specific_Sheet.
  • The Worksheets(“Sales Report”).Activate line ensures that the code runs even if we’re on a different sheet.
  • Using the Add.Name method, we create a new sheet named Balance Sheet before the existing sheet named Profit.

Excel VBA Add Sheet with Name

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

Excel VBA Add Sheet with Name

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

Code Breakdown

  • Similar to Method 2, we define a Sub procedure.
  • We use the After parameter to add the new sheet after the active sheet (which is the sheet named Profit).

Excel VBA Add Sheet with Name

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

Excel VBA Add Sheet with Name

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:

Code Breakdown

  • We create a new sheet before the first sheet (index 1) in the workbook.
  • The sheet is named Company Profile.

Excel VBA Add Sheet with Name

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

Applying Visual Basic for Applications Code

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

Code Breakdown

  • We create a new sheet after the last sheet in the workbook (using Sheets.Count to determine the last sheet index).
  • The sheet is named Income Statement.

Applying Visual Basic for Applications Code

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

Applying Visual Basic for Applications Code


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

Code Breakdown

  • We define a Sub procedure called Add_Multiple_Sheets_Using_Cell_Value.
  • Variable types are specified.
  • An InputBox prompts the user to select the cell range containing the desired sheet names.
  • We activate the Sales Report sheet to ensure the code runs correctly even if we’re on a different sheet.
  • Using a For Each loop, we iterate through each cell in the selected range.
  • The Add.Name method creates a new sheet after the active sheet (Sales Report) and names it based on the value in the current cell.

Applying Visual Basic for Applications Code

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

Applying Visual Basic for Applications Code

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

Applying Visual Basic for Applications Code

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

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo