Add new worksheet with name

different ways to add a worksheet

The code below shows how you can create a new Worksheet and give it a name. Note that with Add the new sheet always becomes the active sheet.

Dim wb As Workbook: Set wb =
Dim strName As String: strName =
Dim ws As Worksheet
Set ws = wb.Worksheets.Add(Type:=xlWorksheet)
With ws
    .Name = strName
End With

The below sections discuss the arguments you can use to control how worksheets are added with specified position: Before and After, Count and what Type possibly using a template Template. At the end there is a macro you can use to Insert sheet with name specified by the user.

Before or After arguments

An object that specifies the sheet Before or After which the new sheet is added. If Before and After are both omitted, the new sheet is inserted before the active sheet.

As an example, below code adds a new worksheet After the active sheet in the active workbook

Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets.Add(Type:=xlWorksheet, After:=Application.ActiveSheet)

The following code puts the new sheet at the beginning:

Set ws = wb.Worksheets.Add(Before:=wb.Worksheets(1), Type:=xlWorksheet)

The following code puts the new sheet at the end:

Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count), Type:=xlWorksheet)

Count

The number of sheets to be added. The default value is one.

Type

Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet or xlChart - or template file path. The default value is xlWorksheet. 

Use Template

You can also insert sheets based on an existing template, see below.

Dim wb As Workbook: Set wb = ActiveWorkbook
Dim ws As Worksheet
Dim strTemplate As String: strTemplate = "C:\temp\tpt.xlsx"
Set ws = wb.Sheets.Add(Type:=strTemplate)

Insert sheet with name specified by the user 

If you often have to insert a worksheet and give it a name the macro below can be handy. The code is build up as follows:

  1. Uses InputBox to ask for the name of the worksheet to be inserted
    • The Title argument gives the pupose of the current action;
    • The Prompt argument specifies what input is required. Some details are added concerning not allowed characters
    • You could use Default argument to automatically fix errors in previously not ok sheet name, or pick up a proposed name somewhere else.
  2. Validates the string returned by the  InputBox :
    1. If empty string the sheet insertion is aborted.
    2. If there already is a sheet with this name
    3. If the string does not comply with excel sheet naming rules
    a MsgBox informs the user about any problems and lets the user either Abort or Retry.
  3. Inserts a worksheet and gives it the name specified in the inputbox.
Sub AddNewWorksheet()
Const cstrTitle As String = "Add new worksheet"
Const cstrPrompt As String = "Give the name for the new worksheet." & vbCrLf & "Not allowed are the characters: : \ / ? * [ and ]"
Dim strInput As String
Dim strDefault As String: strDefault = "" 'setting initial value for inputbox can be useful
Dim strInputErrorMessage As String
Dim booValidatedOk As Boolean: booValidatedOk = False
    On Error GoTo HandleError
    
    Do
        strInput = InputBox(Prompt:=cstrPrompt, Title:=cstrTitle, Default:=strDefault)
        If Len(strInput) = 0 Then GoTo HandleExit
        GoSub ValidateInput
        If Not booValidatedOk Then
            If vbCancel = MsgBox(strInputErrorMessage & "Retry?", vbExclamation + vbOKCancel) Then GoTo HandleExit
        End If
    Loop While Not booValidatedOk
        
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim shts As Sheets: Set shts = wb.Sheets
    Dim obj As Object
    Set obj = shts.Add(Before:=ActiveSheet, Count:=1, Type:=XlSheetType.xlWorksheet)
    obj.Name = strInput
    
HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit
    
ValidateInput:
    If SheetExists(strSheetName:=strInput) Then
        strInputErrorMessage = "Sheet already exists. "
    ElseIf Not IsValidSheetName(strSheetName:=strInput) Then
        strInputErrorMessage = "Sheetname not allowed. "
    Else
        booValidatedOk = True
    End If
    Return
End Sub