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.
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
ActiveSheet
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.NameSheet Name
You are probably most familiar with referencing Sheets by their Tab Name:
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.:
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:
Sheets(Sheets.Count).ActivateSheet “Code Name”
The Sheet Code Name is it’s Object name in VBA:
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:
Workbooks("VBA_Examples.xlsm").Worksheets("Sheet1").ActivateImportant: 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
Sheets("Sheet1").ActivateWhen 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
Sheets("Sheet1").SelectSelect Multiple Sheets
Use an array to select multiple sheets at once:
Worksheets("Sheet1").Activate0Worksheet 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:
Worksheets("Sheet1").Activate1Assign a worksheet to a variable:
Worksheets("Sheet1").Activate2Now you can reference the worksheet variable in your code:
Worksheets("Sheet1").Activate3Loop 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:
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
Workbook protection locks the workbook from structural changes like adding, deleting, moving, or hiding worksheets.
You can turn on workbook protection using VBA:
Worksheets("Sheet1").Activate5or disable workbook protection:
Worksheets("Sheet1").Activate6Note: You can also protect / unprotect without a password by omitting the Password argument:
Worksheets("Sheet1").Activate7
Worksheet Protection
Worksheet-level protection prevents changes to individual worksheets.
Protect Worksheet
Worksheets("Sheet1").Activate8Unprotect Worksheet
Worksheets("Sheet1").Activate9There 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:
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
MsgBox ActiveSheet.Name0Hide Worksheet
MsgBox ActiveSheet.Name1Very Hide Worksheet
MsgBox ActiveSheet.Name2
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:
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.
Worksheet Activate Event
Worksheet activate events run each time the worksheet is opened.
MsgBox ActiveSheet.Name3This 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.