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
- Excel Sheet Checklist Template: How to Build One
- Excel Sales Tracking Template: Free XLS & How to Use
- Excel Schedule Template: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Quick Sort in VBA: Built-In Function or Custom Code?No: VBA does not include a built-in procedure or keyword named QuickSort or quick sort. Call is a…
- VBA AutoFilter Delete: Remove Visible Rows Safely in ExcelYes. In Excel VBA, use SpecialCells(xlCellTypeVisible) on the filtered data rows, then delete those rows; leave the header…
- VBA AutoFilter: What It Filters and How to Use ItVBA AutoFilter filters the rows in an Excel list or table according to criteria in one or more…
- VBA AutoFilter Field 1: What It MeansIn Excel VBA, Field:=1 tells AutoFilter to filter the first column in the filter range—not necessarily worksheet column…
- Excel VBA: How to Delete a Worksheet ButtonTo delete a button from an Excel worksheet, select the button itself and press Delete; for a Form…
- Outlook Excel VBA: Automate Email and Calendar TasksYes—Excel VBA can automate classic Outlook for Windows, including creating email drafts from spreadsheet rows and working with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.