VBA Worksheets: Add, Rename, Hide and Delete Sheets

Part of the free Module 12: Excel VBA Course · Lesson 9 of 18 · Full Excel course

Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.

In Excel VBA a Worksheet object is one sheet tab, and the Worksheets collection holds every ordinary sheet in a workbook. With VBA worksheets you can add, rename, copy, move, delete, hide, very hide, protect and loop through sheets in code, so a workbook can rebuild its own tabs instead of you clicking through them by hand.

Worksheets or Sheets: which collection to use

Worksheets returns only grid worksheets. Sheets returns worksheets and chart sheets, so Sheets.Count can be larger than Worksheets.Count in a workbook that contains a chart sheet. Use Worksheets whenever your code touches cells, because every member of that collection is guaranteed to have a Range. Reach for Sheets only when you genuinely want chart sheets as well.

Always qualify the collection with a workbook. ThisWorkbook.Worksheets("Data") means the sheet in the file that holds the code, while an unqualified Worksheets("Data") means the sheet in whichever workbook happens to be active, which is a common source of data written into the wrong file.

Three ways to refer to a worksheet

There are three ways to point at a sheet, and each one breaks under different circumstances. Store the reference in a variable declared As Worksheet so you get IntelliSense and only have to fix one line if the sheet moves.

Reference	Example	Breaks when	Best for
Tab name	`ThisWorkbook.Worksheets("Data")`	Someone renames the tab	Sheets a user names, or names read from a cell
Index number	`ThisWorkbook.Worksheets(1)`	Someone drags the tab to a new position	Loops, and the first or last sheet
Code name	`shtData.Range("A1")`	Almost never, and never for a user rename	Fixed sheets your macros depend on

The code name is the name shown outside the brackets in the Project Explorer, and you set it in the Properties window under (Name). It is a ready-made object variable: type shtData. anywhere in the project and the sheet responds, with no Set line and no risk from a renamed tab. Code names only work for sheets in the workbook that contains the code.

Add a worksheet and name it

Every VBA module in this lesson starts with Option Explicit on the first line, above all procedures. It forces you to declare variables and catches misspelled sheet names at compile time.

Option Explicit

Sub Add_Worksheet_At_End()
    Dim sh As Worksheet

    Set sh = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    sh.Name = "Data"

    MsgBox "Added sheet: " & sh.Name
End Sub

Worksheets.Add inserts a sheet before the active sheet by default and returns the new sheet, so assigning it to a variable lets you carry on working with it. The After argument sends it to the end of the tab row; use Before:=ThisWorkbook.Worksheets(1) to put it first instead. A sheet name must be unique in the workbook, 31 characters or fewer, and must not contain the characters / ? * [ ] : or a backslash.

Renaming a sheet that already exists is a single line.

Sub Rename_Existing_Sheet()
    ThisWorkbook.Worksheets("Sheet1").Name = "Summary"
End Sub

Delete a worksheet without the confirmation prompt

Delete pops up a confirmation dialog and waits for a click, which stops an unattended macro dead. Switch Application.DisplayAlerts off around the delete and switch it straight back on.

Sub Delete_Worksheet_Quietly()
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Summary").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
End Sub

Setting DisplayAlerts back to True matters. If the macro ends while it is still False, Excel stays silent for the rest of the session and will happily discard unsaved work without asking. A workbook must always keep at least one visible sheet, so the last remaining sheet cannot be deleted.

Copy and move worksheets

Sub Copy_And_Move_Sheets()
    Dim src As Worksheet
    Set src = ThisWorkbook.Worksheets("Data")

    'copy inside the same workbook, placed at the end
    src.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
    ActiveSheet.Name = "Data Backup"

    'move a sheet to the front of the tab row
    ThisWorkbook.Worksheets("Data Backup").Move _
        Before:=ThisWorkbook.Worksheets(1)
End Sub

Copy and Move take the same Before and After arguments. A copy does not return an object, so the copy becomes the active sheet and you rename it through ActiveSheet. Call src.Copy with no arguments at all and Excel creates a brand-new single-sheet workbook containing that sheet, which is the quickest way to send one tab to a colleague.

Hide, very hide and unhide a worksheet

The Visible property has three states, and the third one is the reason people search for this topic.

Constant	Value	What the user sees
`xlSheetVisible`	-1	Normal tab
`xlSheetHidden`	0	Hidden, but listed in the right-click Unhide dialog
`xlSheetVeryHidden`	2	Not in the Unhide dialog at all

Sub Hide_And_Unhide()
    ThisWorkbook.Worksheets("Data").Visible = xlSheetHidden
    ThisWorkbook.Worksheets("Config").Visible = xlSheetVeryHidden
    ThisWorkbook.Worksheets("Data").Visible = xlSheetVisible
End Sub

Use xlSheetVeryHidden for lookup tables, settings and calculation sheets that support a dashboard. A very hidden sheet can only be brought back by VBA or by selecting the sheet in the Project Explorer and changing Visible in the Properties window, so it is tidiness rather than security. Anyone who can open the Visual Basic Editor can reverse it.

Protect and unprotect a worksheet

Sub Protect_Sheet()
    With ThisWorkbook.Worksheets("Summary")
        .Unprotect Password:="pk2026"
        .Range("B2:B10").Locked = False
        .Protect Password:="pk2026", _
                 UserInterfaceOnly:=True, _
                 AllowFiltering:=True
    End With
End Sub

Cells are locked by default, but locking only takes effect once the sheet is protected, so unlock the input cells first. UserInterfaceOnly:=True is the argument worth remembering: it blocks the user but lets your macros keep writing to the sheet without unprotecting it. That setting is not saved with the file, so reapply it from Workbook_Open each time the workbook is opened. Excel sheet passwords are easily removed, so treat protection as a way of preventing accidents, not as security.

Loop through every worksheet

For Each is the natural loop for a collection, and it is safe as long as you are not deleting sheets inside it.

Sub List_All_Sheets()
    Dim sh As Worksheet

    For Each sh In ThisWorkbook.Worksheets
        Debug.Print sh.Index, sh.Name, sh.CodeName, sh.Visible
    Next sh
End Sub

Press Ctrl+G in the editor to see the results in the Immediate window. If you do need to delete sheets while looping, count downwards with For i = Worksheets.Count To 1 Step -1, because removing an item renumbers everything after it and a forward loop then skips sheets.

Check whether a sheet exists

Referring to a sheet that is not there raises run-time error 9. This small function answers the question first, and you can paste it into any module.

Function SheetExists(sheetName As String, _
                     Optional wb As Workbook) As Boolean
    Dim sh As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook

    On Error Resume Next
    Set sh = wb.Worksheets(sheetName)
    On Error GoTo 0

    SheetExists = Not sh Is Nothing
End Function

Sub Test_SheetExists()
    If SheetExists("Data") Then
        MsgBox "Data sheet found"
    Else
        MsgBox "No Data sheet in this workbook"
    End If
End Sub

Worksheet events in brief

Every sheet has its own code module where event procedures run automatically: Change when a cell is edited, SelectionChange when the cursor moves, plus Activate, Deactivate, Calculate and BeforeDoubleClick.

Press Alt+F11 and double-click the sheet name in the Project Explorer.
Change the left drop-down from (General) to Worksheet.
Pick the event in the right drop-down and Excel inserts the empty procedure with the correct arguments.

Worksheet events drop-down list in a sheet code module in the Visual Basic Editor — Choosing a worksheet event in a sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "You selected " & Target.Address
End Sub

Event code belongs in the sheet module, never in a standard module. The full set of workbook and worksheet events, including how to stop an event triggering itself, is covered in Events in VBA.

Worked example: build a sheet index

Suppose the workbook has these tabs and you want a front page that links to each one.

Sheet	Visible
Jan	Visible
Feb	Visible
Config	Very hidden

Sub Build_Index_Sheet()
    Dim idx As Worksheet, sh As Worksheet
    Dim r As Long

    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("Index").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set idx = ThisWorkbook.Worksheets.Add(Before:=ThisWorkbook.Worksheets(1))
    idx.Name = "Index"
    idx.Range("A1").Value = "Sheet"
    idx.Range("B1").Value = "Visible"
    idx.Range("A1:B1").Font.Bold = True

    r = 2
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> idx.Name Then
            idx.Hyperlinks.Add Anchor:=idx.Cells(r, 1), Address:="", _
                SubAddress:="'" & sh.Name & "'!A1", TextToDisplay:=sh.Name
            idx.Cells(r, 2).Value = (sh.Visible = xlSheetVisible)
            r = r + 1
        End If
    Next sh

    idx.Columns("A:B").AutoFit
End Sub

The result is an Index tab at the front listing Jan, Feb and Config as clickable links, with TRUE, TRUE and FALSE in column B. Run it again after adding a sheet and it rebuilds itself without a single prompt.

Tips and common mistakes

Qualify every sheet with a workbook. ThisWorkbook.Worksheets("Data") is safe, a bare Worksheets("Data") follows whichever file is active.
Use code names for sheets your macros depend on. A user renaming a tab then cannot break the code.
Never leave DisplayAlerts off. Reset it to True immediately, and also inside your error handler.
Skip Select and Activate. sh.Range("A1").Value = 1 works on any sheet, including a hidden one, and runs far faster.
Delete in a backwards loop. A forward For i = 1 To Count loop skips sheets as the index shifts.
Check the name before you use it. Trailing spaces in a tab name are invisible and cause error 9.
Protect with UserInterfaceOnly and reapply it on open, because Excel does not save that flag with the file.

Errors and how to fix them

Error	Cause	Fix
Run-time error 9: Subscript out of range	Sheet name misspelled, renamed, or in another workbook	Test with the `SheetExists` function, or use the code name
Run-time error 1004: That name is already taken	Renaming a sheet to a name already in use	Check with `SheetExists` before setting `Name`
Run-time error 1004 on `Visible`	Hiding the only visible sheet left	Make another sheet visible first
Run-time error 1004 on a write	The sheet is protected	Unprotect, or protect with `UserInterfaceOnly:=True`
Object variable not set (error 91)	`Set` omitted when assigning a worksheet	Write `Set sh = ThisWorkbook.Worksheets("Data")`

Watch the video walkthrough

Click here to download the practice file.

Practice exercise

Write a macro that adds twelve sheets named Jan to Dec at the end of the workbook, using a loop and SheetExists so it can be run twice without error.
Very hide every sheet whose name begins with Config, then write a second macro that makes them all visible again.
Copy the Data sheet to a new workbook and leave the original untouched.
Protect the Summary sheet with UserInterfaceOnly:=True, leaving B2:B10 unlocked, and confirm a macro can still write to a locked cell.
Run Build_Index_Sheet, add a new tab, run it again and check the index rebuilds with no prompt.

Key takeaways

Worksheets holds grid sheets only; Sheets also holds chart sheets.
Refer to a sheet by tab name, index or code name, and prefer the code name for sheets your macros rely on.
Worksheets.Add returns the new sheet, so set a variable and rename it in the next line.
Wrap Delete in Application.DisplayAlerts = False and True to skip the prompt.
Visible takes three values, and xlSheetVeryHidden keeps a sheet out of the Unhide dialog.
Loop with For Each sh In ThisWorkbook.Worksheets, and test with SheetExists before touching a sheet by name.

Related lessons

Frequently asked questions

What is the difference between Sheets and Worksheets in VBA?

Worksheets returns only grid worksheets, while Sheets returns worksheets and chart sheets together. In a workbook with a chart sheet, Sheets.Count is larger than Worksheets.Count. Use Worksheets whenever the code reads or writes cells, because every item in that collection is certain to have a Range.

How do I delete a worksheet in VBA without the confirmation message?

Set Application.DisplayAlerts to False, call the Delete method on the sheet, then set DisplayAlerts back to True on the very next line. Leaving it False for the rest of the macro suppresses every other Excel warning too, including the prompt to save changes when a workbook closes.

How do I unhide a very hidden sheet?

Run one line of code, sh.Visible = xlSheetVisible, or open the Visual Basic Editor with Alt+F11, select the sheet in the Project Explorer and change Visible to xlSheetVisible in the Properties window. A very hidden sheet never appears in the Unhide dialog in Excel itself.

What is a worksheet code name and why should I use it?

The code name is the internal name shown in the Project Explorer and set under (Name) in the Properties window. It works as a ready-made object variable, so shtData.Range(A1) keeps working even after a user renames the tab. Use it for every sheet your macros depend on.

How do I check if a sheet exists before using it?

Write a small function that tries to Set a Worksheet variable inside On Error Resume Next, then returns Not sh Is Nothing. Calling it before you touch a sheet by name avoids run-time error 9, subscript out of range, which is the most common worksheet error in VBA.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.