To add a worksheet with VBA in Excel, use Worksheets.Add. For example, this macro adds a sheet after the workbook’s last worksheet and names it Summary:
Sub AddSummaryWorksheet()
Dim ws As Worksheet
' Add a worksheet after the last existing worksheet.
Set ws = ThisWorkbook.Worksheets.Add( _
After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
ws.Name = "Summary"
End Sub
Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z. The example uses ThisWorkbook, meaning the workbook that contains the code; that distinction matters when a macro is stored somewhere other than the workbook being edited.
What Worksheets.Add Does
Worksheets.Add creates a worksheet and returns a reference to the new sheet. If you omit its position arguments, Excel inserts the new worksheet before the active sheet. That default can be surprising in a macro, because the active sheet may differ depending on what the user clicked or how the procedure was started. Specifying Before or After makes placement predictable.
The method also accepts optional arguments such as Count and Type. Count controls how many sheets to add; if you do not provide it, one is added. For ordinary worksheets, leave Type out. The method can also create other sheet types, such as chart sheets, but those are not worksheets and do not belong to the Worksheets collection.
Assigning the result to a variable, as in Set ws = Worksheets.Add(...), lets the rest of the macro refer directly to the new worksheet. This is safer than relying on whichever sheet happens to be active after insertion. You can then name the sheet, write values to it, or format it through ws.
Choose Where the New Sheet Goes
Use After to put a worksheet after a known sheet, or Before to put it in front of one. To append a worksheet to the end, use the last worksheet in the workbook as the After target, as in the opening example. To place one at the beginning, use the first worksheet as the Before target:
Sub AddInputWorksheetFirst()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
' Insert before the first worksheet in this workbook.
Set ws = wb.Worksheets.Add(Before:=wb.Worksheets(1))
ws.Name = "Inputs"
End Sub
You can target a named worksheet instead of the first or last one. The following example inserts a sheet immediately after a worksheet named Read Me:
Sub AddWorksheetAfterReadMe()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
' Read Me must exist as a worksheet in this workbook.
Set ws = wb.Worksheets.Add(After:=wb.Worksheets("Read Me"))
ws.Name = "Details"
End Sub
If the named target does not exist, the code stops with an error when it tries to find it. In a procedure used by other people, check that the target sheet exists first or handle the error deliberately. Also note that Worksheets.Count counts worksheets, not every possible sheet tab. That is appropriate when adding after the last worksheet; use the broader Sheets collection when your logic specifically needs to account for chart sheets too.
Name the Worksheet and Avoid Duplicate Names
Excel requires sheet names to be unique within a workbook. A name cannot be blank, exceed 31 characters, or contain characters such as a colon, slash, backslash, question mark, asterisk, or square brackets. A fixed name such as Summary works until the macro runs a second time: the new sheet is created, but assigning the already-used name causes an error.
One practical approach is to add a numbered suffix whenever the base name is already in use. This complete example checks all sheet tabs, including chart sheets, before choosing a name:
Sub AddNumberedReportWorksheet()
Dim wb As Workbook
Dim ws As Worksheet
Dim baseName As String
Dim candidate As String
Dim suffix As Long
Set wb = ThisWorkbook
baseName = "Monthly Report"
candidate = baseName
suffix = 2
' Find an unused name, including names of chart sheets.
Do While SheetNameExists(wb, candidate)
candidate = baseName & " (" & suffix & ")"
suffix = suffix + 1
Loop
Set ws = wb.Worksheets.Add( _
After:=wb.Worksheets(wb.Worksheets.Count))
ws.Name = candidate
End Sub
Private Function SheetNameExists(ByVal wb As Workbook, _
ByVal sheetName As String) As Boolean
Dim sh As Object
' Compare without regard to capitalization.
For Each sh In wb.Sheets
If StrComp(sh.Name, sheetName, vbTextCompare) = 0 Then
SheetNameExists = True
Exit Function
End If
Next sh
End Function
On its first run, this names the sheet Monthly Report; on later runs it uses Monthly Report (2), Monthly Report (3), and so on. The base name in this example is short and contains no prohibited characters. If a name comes from a cell or user input, validate its length and characters before assigning it; a duplicate-name check alone does not make an invalid name acceptable.
Add Several Worksheets at Once
To add multiple worksheets, pass a number to the Count argument. Excel gives the new sheets default names, so a loop is useful if you want to rename each one. This example appends three worksheets and gives them distinct names:
Sub AddQuarterWorksheets()
Dim wb As Workbook
Dim i As Long
Dim ws As Worksheet
Set wb = ThisWorkbook
' Add three worksheets after the current last worksheet.
wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count), _
Count:=3
For i = 1 To 3
Set ws = wb.Worksheets(wb.Worksheets.Count - 3 + i)
ws.Name = "Quarter " & i
Next i
End Sub
The new worksheets are added together at the end, and the loop refers to those final three positions. This example assumes the names Quarter 1 through Quarter 3 are not already taken. If the macro may run repeatedly, use a naming strategy that checks for existing names, or remove and recreate the previous generated sheets only when that is safe for the workbook’s data.
Where to Put and How to Run the Macro
In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, paste a complete procedure into the module, and return to Excel. Run a macro from the editor by placing the cursor inside its procedure and pressing F5, or return to Excel and press Alt+F8, select the macro, and choose Run. If the Developer tab is visible, Developer > Visual Basic opens the editor and Developer > Macros opens the macro list.
If the Developer tab is not shown, enable it through File > Options > Customize Ribbon, then select Developer in the Main Tabs list. On Mac, use the Developer tab’s Visual Basic command if available; keyboard shortcuts can vary with the keyboard and function-key settings. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, edit, or run VBA macros. Use the desktop Excel application for these steps.
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want its code to remain in that file. Saving as a standard .xlsx file does not preserve VBA macros. If Excel displays a security warning, only enable macros when you trust the source and understand what the code will change. Do not change security settings to enable every macro indiscriminately.
Common Errors and How to Fix Them
- Run-time error while naming the sheet: The name may already exist, be too long, or include a prohibited character. Check the name before assigning it, and keep generated names within Excel’s limits.
- Subscript out of range: A referenced worksheet name may be misspelled or absent from the workbook object used by the code. Confirm the exact tab name and whether the macro should use
ThisWorkbookorActiveWorkbook. - Cannot add or move a sheet: The workbook’s structure may be protected. Review the workbook protection settings and, if appropriate, unprotect the workbook through Review > Protect Workbook before running the macro. Do not remove protection unless you are authorized to do so.
- The sheet appears in the wrong file:
ThisWorkbookrefers to the workbook containing the VBA code. If the code is in a personal macro workbook or an add-in, that may not be the workbook you are viewing. UseActiveWorkbookonly when the active workbook is intentionally the target, and make sure it is the correct file before making changes. - The macro does not run: Confirm that the file is saved as
.xlsm, that the macro is in a standard module, and that Excel has not blocked the code. A workbook opened in Excel for the web cannot run the VBA procedure.
Some errors can occur after the worksheet has already been inserted. For example, if the macro adds a sheet and then fails while naming it, an unnamed default worksheet may remain. Check the workbook after an interrupted run rather than repeatedly running the procedure without inspecting its state.
VBA Compared with Adding a Sheet Manually
For a one-time change, using the worksheet plus button beside the sheet tabs is usually quicker than writing a macro. You can also use Home > Insert > Insert Sheet in desktop Excel. VBA is useful when the task is repeated, when a worksheet must be created as part of a larger process, or when its position and name need to follow consistent rules.
A macro can also prepare the new sheet after inserting it—for example, by writing headings or setting a tab color. Those extra actions should be explicit in the code. Adding a worksheet does not automatically copy the layout or formulas from another sheet; if you need a template, copy an existing worksheet deliberately and account for its formulas, references, and name.
Frequently Asked Questions
What is the simplest VBA code to add a worksheet?
Use Worksheets.Add, such as Set ws = ThisWorkbook.Worksheets.Add, after declaring ws As Worksheet. With no position specified, Excel inserts the new worksheet before the active sheet.
How do I add a worksheet at the end of a workbook?
Set the After argument to the workbook’s last worksheet: Worksheets.Add After:=Worksheets(Worksheets.Count). Qualify both references with the intended workbook to avoid depending on whichever workbook is active.
How can I add a worksheet with a specific name?
Store the new worksheet returned by Worksheets.Add in a variable, then assign its Name property. Check first that the name is unique and follows Excel’s naming rules.
Can VBA add more than one worksheet?
Yes. Use the Count argument, for example Worksheets.Add Count:=3, and then refer to the newly created worksheets to rename or prepare them. Confirm that the names you assign do not already exist.
Can I run this worksheet macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in the desktop version of Excel for Windows or Mac to run the procedure.
Can I undo a worksheet added by VBA with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z cannot reverse the macro’s changes. Save a separate copy before running code that edits a workbook.
Related
- Excel Data Validation Guidelines for Reliable Spreadsheets
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Bold Text: Limits and Better AlternativesNo: VBA’s built-in MsgBox cannot make selected words bold or apply rich-text formatting. If you need bold text,…
- Microsoft Excel VBA and Macro Training: What to LearnMicrosoft Excel VBA and macro training teaches you to automate repetitive workbook tasks, first by recording simple macros…
- Excel VBA Function Not Updating Automatically? Fix ItIf an Excel VBA function is not updating automatically, first check Formulas > Calculation Options > Automatic, then…
- Excel VBA: Extract the Year from a Date in a WorksheetTo extract a year from a date in Excel VBA, use the built-in Year function: pass it a…
- Excel VBA MATCH Error Handling: Avoid Run-Time ErrorsFor safer Excel VBA MATCH error handling, use Application.Match and test its result with IsError before using it.…
- Creating Macros in Google Sheets: A Practical GuideTo create a macro in Google Sheets, open the spreadsheet in a web browser and choose Extensions >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.