Lesson 19: Mastering the Worksheet Object in Excel VBA 365

19.1 The Worksheet Properties

Similar to the Range Object, the Worksheet has its own set of properties and methods. When we write Excel VBA 365 code involving the Worksheet object, we use Worksheets.

Some of the common properties of the worksheet are name, count, cells, columns, rows and columnWidth.

Example 19.1: Accessing Worksheet Name

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Name
End Sub
    

Running the code will produce a pop-up dialog that displays the worksheet name as sheet 1, as shown in Figure 19.1.

Example 19.2: Counting Worksheets

The count method returns the number of worksheets in an opened workbook.

Private Sub CommandButton1_Click()
 MsgBox Worksheets.Count
End Sub
    

Example 19.3: Counting Columns

The count method in this example will return the number of columns in the worksheet.

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Columns.Count
End Sub
    

Example 19.4: Counting Rows

The count method in this example will return the number of rows in the worksheet.

Private Sub CommandButton1_Click()
 MsgBox Worksheets(1).Rows.Count
End Sub
    

19.2 The Worksheet Methods

Some of the worksheet methods are add, delete, select, SaveAs, copy, paste and more.

Example 19.5: Adding and Deleting Worksheets

In this example, when the user clicks the first command button, it will add a new sheet to the workbook. When the user clicks the second command button, it will delete the new worksheet that has been added earlier.

Private Sub CommandButton1_Click()
 Worksheets.Add
End Sub

Private Sub CommandButton2_Click()
 Worksheets(1).Delete
End Sub
    

Example 19.6: Selecting Worksheets

The select method associated with worksheet lets the user select a particular worksheet. In this example, worksheet 2 will be selected.

Private Sub CommandButton1_Click()
'Worksheet 2 will be selected
 Worksheets(2).Select
End Sub
    

The select method can also be used together with the Worksheet's properties Cells, Columns, and Rows as shown in the following examples.

Example 19.7: Selecting Cells

Private Sub CommandButton1_Click()
'Cell A1 will be selected
 Worksheets(1).Cells(1).Select
End Sub
    

Example 19.8: Selecting Columns

Private Sub CommandButton1_Click()
'Column 1 will be selected
 Worksheets(1).Columns(1).Select
End Sub
    

Example 19.9: Selecting Rows

Private Sub CommandButton1_Click()
'Row 1 will be selected
 Worksheets(1).Rows(1).Select
End Sub
    

Example 19.10: Copy and Paste Operations

Excel VBA 365 also allows us to write code for copy and paste. Let's look at the following Example:

Private Sub CommandButton1_Click()
'To copy the content of a cell 1
 Worksheets(1).Cells(1).Select
 Selection.Copy
End Sub
    

Private Sub CommandButton2_Click()
'To paste the content of cell 1 to cell 2
 Worksheets(1).Cells(2).Select
 ActiveSheet.Paste
End Sub
    

Example 19.11: Protecting and Unprotecting Worksheets

Private Sub CommandButton3_Click()
    'Protect worksheet with password
    Worksheets("Sheet1").Protect Password:="mypassword", _
        AllowFormattingCells:=True
End Sub

Private Sub CommandButton4_Click()
    'Unprotect worksheet
    Worksheets("Sheet1").Unprotect Password:="mypassword"
End Sub
    

This example shows how to secure your worksheet with password protection while allowing cell formatting.

19.3 Worksheet Object Best Practices

• Always reference worksheets by name (e.g., Worksheets("Data")) rather than index number when possible
• Use Worksheet.Activate before making changes to ensure you're working with the correct sheet
• Implement error handling when deleting worksheets to prevent accidental data loss
• Consider worksheet visibility (Visible property) for better user experience
• Use UsedRange property to work with only the cells that contain data
• Disable screen updating (Application.ScreenUpdating = False) when making multiple worksheet changes

🔗 Related Resources

• Excel VBA Classic Tutorial
• Excel VBA Examples
• Excel VBA 2010 Tutorial
• Visual Basic Tutorial