Excel VBA: Add Sheet and Name It (Step-by-Step)

VBA & macros · Updated November 22, 2026

To add a new worksheet and name it with Excel VBA, use Sheets.Add followed by setting the Name property, for example: Sheets.Add.Name = "MySheet". This single line creates a new sheet and immediately assigns it the name you specify, and it works in Excel for Microsoft 365, Excel 2021, 2019, and earlier versions on Windows and Mac.

Why Use VBA to Add and Name Sheets

Manually adding and renaming sheets is fine for one-off tasks, but VBA becomes essential when you need to do it repeatedly, for many sheets at once, or as part of a larger automated workflow. For example, you might generate a new sheet for each month of the year, each department in a company, or each item in a list. Doing this by hand is slow and error-prone; a short macro can do it in a fraction of a second and with consistent naming every time. VBA also lets you control the sheet position, the sheet type (worksheet vs. chart sheet), and what happens if a sheet with that name already exists—something the manual ribbon commands don’t offer.

The Core Command: Sheets.Add

The Sheets.Add method is the foundation. It belongs to the Sheets collection, which includes both worksheets and chart sheets. The simplest form is just Sheets.Add, which adds a new sheet before the active sheet. To name it in the same line, you chain the Name property:

Sheets.Add.Name = "Data"

This creates a new sheet named “Data” and makes it the active sheet. If a sheet named “Data” already exists, Excel raises an error (runtime error 1004). To avoid that, you can check for existence first or use a unique name. The method also accepts arguments, but the most useful is After or Before to control placement:

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Summary"

This adds a new sheet after the last sheet in the workbook. Note that the parentheses are required when using named arguments.

How to Name the New Sheet Dynamically

Often you don’t want a hard-coded name; you want to derive it from a cell value, a variable, or a loop counter. Here’s a common pattern:

Dim newName As String
newName = Range("A1").Value ' get name from cell A1
Sheets.Add.Name = newName

If you’re looping through a list of names in column A (say A2:A10), you can create one sheet per name:

Dim cell As Range
For Each cell In Range("A2:A10")
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell.Value
Next cell

This adds a sheet for each non-empty cell in that range. Be careful: if any of those names contain characters invalid for sheet names (like : or \) or exceed 31 characters, the macro will stop with an error. You can sanitize the name first using Replace and Left.

Choosing Between Worksheets.Add and Sheets.Add

You’ll see both Worksheets.Add and Sheets.Add in code online. The difference matters: Sheets includes chart sheets and macro sheets, while Worksheets only includes regular worksheet tabs. For 99% of cases where you want a normal data tab, use Worksheets.Add to be explicit and avoid accidentally creating a chart sheet:

Worksheets.Add.Name = "Report"

If you ever need a chart sheet, you’d use Sheets.Add with the Type argument set to xlChart. For everyday sheet creation, Worksheets.Add is the safer, clearer choice.

Common Problems and How to Avoid Them

The most frequent issue is the “Name already exists” error. Before naming, check if the sheet exists:

Function SheetExists(sName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(sName)
    SheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

Then use it:

If Not SheetExists("Data") Then
    Worksheets.Add.Name = "Data"
Else
    MsgBox "Sheet already exists"
End If

Another problem: invalid characters in names. Excel forbids : \ / ? * [ ] and names cannot be blank or start or end with an apostrophe. A quick sanitizer:

Dim cleanName As String
cleanName = Replace(Range("A1").Value, ":", "_")
cleanName = Replace(cleanName, "\\", "_")
' repeat for other invalid chars...
If Len(cleanName) > 31 Then cleanName = Left(cleanName, 31)
Worksheets.Add.Name = cleanName

Also note that sheet names are case-insensitive but preserve case. “data” and “Data” are considered the same name.

Alternatives to VBA for Adding Sheets

If you don’t want to use VBA, you have built-in options. The quickest is clicking the plus (+) icon next to the sheet tabs, which adds a new sheet named “Sheet1”, “Sheet2”, etc. To rename, double-click the tab and type. You can also right-click a tab, choose Insert, then select Worksheet. For repeating patterns, you can use Excel’s Fill Handle to copy a sheet multiple times, but you’ll still rename each manually. If you need dynamic names based on cell values without VBA, you can use a formula in a cell and then manually rename the sheet by referencing that cell—but that’s not automatic. For a fully automated solution without VBA, consider Power Query to create new sheets, but that requires a data source and is more complex than the simple VBA approach.

Safety and Best Practices for Running Macros

Before running any VBA that adds sheets, save a copy of your workbook. Macros cannot be undone with Ctrl+Z; if the macro deletes or overwrites data, you can’t revert. Also, if your workbook contains formulas referencing sheets that get renamed or deleted, those references can break. To be safe, always test on a copy first. When you write the macro, declare variables with Dim and use Option Explicit at the top of the module to catch typos. Here’s a complete, safe macro:

Option Explicit

Sub AddNamedSheet()
    Dim ws As Worksheet
    Dim sName As String
    
    sName = InputBox("Enter sheet name:")
    If sName = "" Then Exit Sub
    
    ' Check for invalid characters
    If sName Like "*[:\\/?*[]]*" Then
        MsgBox "Invalid character in name"
        Exit Sub
    End If
    
    ' Check if exists
    On Error Resume Next
    Set ws = Worksheets(sName)
    On Error GoTo 0
    If Not ws Is Nothing Then
        MsgBox "Sheet already exists"
        Exit Sub
    End If
    
    ' Add and name
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sName
End Sub

To run this, press Alt+F11 to open the VBA editor, insert a module, paste the code, then press F5 while in the code. On Mac, use Fn+Option+F11 to open the editor.

Frequently Asked Questions

Can I add multiple sheets and name them in one line? No, you must add each sheet individually. You can use a loop as shown above, or use Sheets.Add Count:=3 to add three sheets at once, but then you’d have to rename them in a loop afterward because they get default names.

How do I name a sheet after the current date? Use Worksheets.Add.Name = Format(Date, "yyyy-mm-dd"). This creates a sheet like “2025-04-14”. Be aware that date format may include slashes if you use a different format string, which are invalid in sheet names.

What happens if I try to name a sheet “History” but it already exists? You get runtime error 1004. To handle it gracefully, use the SheetExists function shown earlier, or wrap the naming in On Error Resume Next and check Err.Number.

Does this work in Excel for the web? No. Excel for the web does not support VBA macros. You can only add and rename sheets manually in the browser. The VBA code in this article works only in the desktop versions of Excel for Windows and Mac.

Can I name a sheet with a space or special characters? Spaces are allowed, but the following are not: : \ / ? * [ ]. Also, names cannot be blank and cannot start or end with an apostrophe. Names are limited to 31 characters.

How do I rename the active sheet without adding a new one? Use ActiveSheet.Name = "NewName". This changes the name of the currently selected sheet. The same naming rules and error conditions apply.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.