VBA Sheets - The Ultimate Guide - Automate Excel (2023)

In this Article

  • Sheets Vs. Worksheets
  • Referencing Sheets
    • ActiveSheet
    • Sheet Name
    • Sheet Index Number
    • Sheet “Code Name”
    • Referencing Sheets in Other Workbooks
  • Activate vs. Select Sheet
    • Activate a Sheet
    • Select a Sheet
    • Select Multiple Sheets
  • Worksheet Variable
  • Loop Through All Sheets in Workbook
  • Worksheet Protection
    • Workbook Protection
    • Worksheet Protection
    • Protect Worksheet
    • Unprotect Worksheet
  • Worksheet Visible Property
    • Unhide Worksheet
    • Hide Worksheet
    • Very Hide Worksheet
  • Worksheet-Level Events
    • Worksheet Activate Event
    • Worksheet Change Event
  • Worksheet Cheat Sheet
  • VBA Worksheets Cheatsheet

This is the ultimate guide to working with Excel Sheets / Worksheets in VBA.

At the bottom of this guide, we’ve created a cheat sheet of common commands for working with sheets.

Sheets Vs. Worksheets

There are two ways to reference Sheets using VBA. The first is with the Sheets object:


The other is with the Worksheets object:


99% of the time, these two objects are identical. In fact, if you’ve searched online for VBA code examples, you’ve probably seen both objects used. Here is the difference:

The Sheets Collection contains Worksheets AND Chart Sheets.

VBA Sheets - The Ultimate Guide - Automate Excel (1)

So use Sheets if you want to include regular Worksheets AND Chart Sheets. Use Worksheets if you want to exclude Chart Sheets. For the rest of this guide we will use Sheets and Worksheets interchangeably.

Referencing Sheets

There are several different ways to reference Sheets:

  • ActiveSheet
  • Sheet Tab Name
  • Sheet Index Number
  • Sheet Code Name


The ActiveSheet is the Sheet that’s currently active. In other words, if you paused your code and looked at Excel, it’s the sheet that is visible. The below code example will display a MessageBox with the ActiveSheet name.

MsgBox ActiveSheet.Name

Sheet Name

You are probably most familiar with referencing Sheets by their Tab Name:

VBA Sheets - The Ultimate Guide - Automate Excel (2)


This is the sheet name that’s visible to Excel users. Enter it into the sheets object, as a string of text, surrounded by quotations.

Sheet Index Number

The Sheet Index number is the sheet position in the workbook. 1 is the first sheet. 2 is the second sheet etc.:

VBA Sheets - The Ultimate Guide - Automate Excel (3)


Sheet Index Number – Last Sheet in Workbook

To reference the last Sheet in the workbook, use Sheets.Count to get the last Index Number and activate that sheet:


Sheet “Code Name”

The Sheet Code Name is it’s Object name in VBA:

VBA Sheets - The Ultimate Guide - Automate Excel (4)


VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More

Referencing Sheets in Other Workbooks

It’s also easy to reference Sheets in other Workbooks. To do so, you need to use the Workbooks Object:


Important: The Workbook must be open before you can reference its Sheets.

Activate vs. Select Sheet

In another article we discuss everything about activating and selecting sheets. The short version is this:

When you Activate a Sheet it becomes the ActiveSheet. This is the sheet you would see if you looked at your Excel program. Only one sheet may be activate at a time.

Activate a Sheet


When you select a Sheet, it also becomes the ActiveSheet. However, you can select multiple sheets at once. When multiple sheets are selected at once, the “top” sheet is the ActiveSheet. However, you can toggle the ActiveSheet within selected sheets.

VBA Programming | Code Generator does work for you!

Select a Sheet


Select Multiple Sheets

Use an array to select multiple sheets at once:

Worksheets(Array("Sheet2", "Sheet3")).Select

Worksheet Variable

Assigning a worksheet to an object variable allows you to reference the worksheet by it’s variable name. This can save a lot of typing and make your code easier to read. There are also many other reasons you might want to use variables.

To declare a worksheet variable:

Dim ws as worksheet

Assign a worksheet to a variable:

Set ws = Sheets("Sheet1")

Now you can reference the worksheet variable in your code:


Loop Through All Sheets in Workbook

Worksheet variables are useful when you want to loop through all the worksheets in a workbook. The easiest way to do this is:

Dim ws as WorksheetFor Each ws in Worksheets MsgBox ws.nameNext ws

This code will loop through all worksheets in the workbook, displaying each worksheet name in a message box. Looping through all the sheets in a workbook is very useful when locking / unlocking or hiding / unhiding multiple worksheets at once.

Worksheet Protection

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Workbook Protection

VBA Sheets - The Ultimate Guide - Automate Excel (6)

Workbook protection locks the workbook from structural changes like adding, deleting, moving, or hiding worksheets.

You can turn on workbook protection using VBA:

ActiveWorkbook.Protect Password:="Password"

or disable workbook protection:

ActiveWorkbook.UnProtect Password:="Password"

Note: You can also protect / unprotect without a password by omitting the Password argument:


Worksheet Protection

Worksheet-level protection prevents changes to individual worksheets.

Protect Worksheet

Worksheets("Sheet1").Protect "Password"

Unprotect Worksheet

Worksheets("Sheet1").Unprotect "Password"

There are a variety of options when protecting worksheets (allow formatting changes, allow user to insert rows, etc.) We recommend using the Macro Recorder to record your desired settings.

We discuss worksheet protection in more detail here.

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Worksheet Visible Property

You might already know that worksheets can be hidden:

VBA Sheets - The Ultimate Guide - Automate Excel (7)

There are actually three worksheet visibility settings: Visible, Hidden, and VeryHidden.Hidden sheets can be unhidden by any regular Excel user – by right-clicking in the worksheet tab area (shown above). VeryHidden sheets can only be unhidden with VBA code or from within the VBA Editor. Use the following code examples to hide / unhide worksheets:

Unhide Worksheet

Worksheets("Sheet1").Visible = xlSheetVisible

Hide Worksheet

Worksheets("Sheet1").visible = xlSheetHidden

Very Hide Worksheet

Worksheets("Sheet1").Visible = xlSheetVeryHidden

AutoMacro | Ultimate VBA Add-in | Click for Free Trial!

Worksheet-Level Events

Events are triggers that can cause “Event Procedures” to run. For example, you can cause code to run every time any cell on a worksheet is changed or when a worksheet is activated.

Worksheet event procedures must be placed in a worksheet module:

VBA Sheets - The Ultimate Guide - Automate Excel (8)

There are numerous worksheet events. To see a complete list, go to a worksheet module, select “Worksheet” from the first drop-down. Then selecting an event procedure from the second drop-down to insert it into the module.

VBA Sheets - The Ultimate Guide - Automate Excel (9)

Worksheet Activate Event

Worksheet activate events run each time the worksheet is opened.

Private Sub Worksheet_Activate() Range("A1").SelectEnd Sub

This code will select cell A1 (resetting the view area to the top-left of the worksheet) each time the worksheet is opened.

Worksheet Change Event

Worksheet change events run whenever a cell value is changed on the worksheet. Read our tutorial about Worksheet Change Events for more information.

Worksheet Cheat Sheet

Below you will find a cheat sheet containing common code examples for working with sheets in VBA

VBA Worksheets Cheatsheet

VBA worksheets Cheatsheet

DescriptionCode Example
Referencing and Activating Sheets
Tab NameSheets("Input").Activate
VBA Code NameSheet1.Activate
Index PositionSheets(1).Activate
Select Sheet
Select SheetSheets("Input").Select
Set to VariableDim ws as Worksheet
Set ws = ActiveSheet
Name / RenameActiveSheet.Name = "NewName"
Next SheetActiveSheet.Next.Activate
Loop Through all SheetsDim ws as Worksheet

For each ws in Worksheets
Msgbox ws.name
Next ws

Loop Through Selected SheetsDim ws As Worksheet

For Each ws In ActiveWindow.SelectedSheets
MsgBox ws.Name
Next ws

Get ActiveSheetMsgBox ActiveSheet.Name
Add SheetSheets.Add
Add Sheet and NameSheets.Add.Name = "NewSheet"
Add Sheet With Name From CellSheets.Add.Name = range("a3").value
Add Sheet After AnotherSheets.Add After:=Sheets("Input")
Add Sheet After and NameSheets.Add(After:=Sheets("Input")).Name = "NewSheet"
Add Sheet Before and NameSheets.Add(Before:=Sheets("Input")).Name = "NewSheet"
Add Sheet to End of WorkbookSheets.Add After:=Sheets(Sheets.Count)
Add Sheet to Beginning of WorkbookSheets.Add(Before:=Sheets(1)).Name = "FirstSheet"
Add Sheet to VariableDim ws As Worksheet
Set ws = Sheets.Add
Copy Worksheets
Move Sheet to End of WorkbookSheets("Sheet1").Move After:=Sheets(Sheets.Count)
To New WorkbookSheets("Sheet1").Copy
Selected Sheets To New WorkbookActiveWindow.SelectedSheets.Copy
Before Another SheetSheets("Sheet1").Copy Before:=Sheets("Sheet2")
Before First SheetSheets("Sheet1").Copy Before:=Sheets(1)
After Last SheetSheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Copy and NameSheets("Sheet1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "LastSheet"
Copy and Name From Cell ValueSheets("Sheet1").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("A1").Value
To Another WorkbookSheets("Sheet1").Copy Before:=Workbooks("Example.xlsm").Sheets(1)
Hide / Unhide Sheets
Hide SheetSheets("Sheet1").visible = False
Sheets("Sheet1").visible = xlSheetHidden
Unhide SheetSheets("Sheet1").Visible = True
Sheets("Sheet1").Visible = xlSheetVisible
Very Hide SheetSheets(“Sheet1”).Visible = xlSheetVeryHidden
Delete or Clear Sheets
Delete SheetSheets("Sheet1").Delete
Delete Sheet (Error Handling)On Error Resume Next
On Error GoTo 0
Delete Sheet (No Prompt)Application.DisplayAlerts = False
Application.DisplayAlerts = True
Clear SheetSheets("Sheet1").Cells.Clear
Clear Sheet Contents OnlySheets("Sheet1").Cells.ClearContents
Clear Sheet UsedRangeSheets("Sheet1").UsedRange.Clear
Protect or Unprotect Sheets
Unprotect (No Password)Sheets("Sheet1").Unprotect
Unprotect (Password)Sheets("Sheet1").Unprotect "Password"
Protect (No Password)Sheets("Sheet1").Protect
Protect (Password)Sheets("Sheet1").Protect "Password"
Protect but Allow VBA AccessSheets("Sheet1").Protect UserInterfaceOnly:=True
Unprotect All SheetsDim ws As Worksheet

For Each ws In Worksheets
ws.Unprotect "password"
Next ws

Top Articles
Latest Posts
Article information

Author: Ray Christiansen

Last Updated: 11/29/2022

Views: 6656

Rating: 4.9 / 5 (49 voted)

Reviews: 80% of readers found this page helpful

Author information

Name: Ray Christiansen

Birthday: 1998-05-04

Address: Apt. 814 34339 Sauer Islands, Hirtheville, GA 02446-8771

Phone: +337636892828

Job: Lead Hospitality Designer

Hobby: Urban exploration, Tai chi, Lockpicking, Fashion, Gunsmithing, Pottery, Geocaching

Introduction: My name is Ray Christiansen, I am a fair, good, cute, gentle, vast, glamorous, excited person who loves writing and wants to share my knowledge and understanding with you.