Lesson 20: The worksheet object

20.1 The Worksheet Properties in Excel VBA

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

The reason is that we are dealing with a collection of worksheets most of the time, so using Worksheets enables us to manipulate multiple worksheets at the same time. Some of the common properties of the worksheet are name, count, cells, columns, rows and columnWidth.

Example 20.1

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 20.1.

Example 20.2

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

Private Sub CommandButton1_Click() 
 MsgBox Worksheets.Count 
End Sub

The output is shown in Figure 20.2.

Example 20.3

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

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

The output is shown below:

Example 20.4

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

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

20.2 The Worksheet Methods

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

Example 20.5

Private Sub CommandButton1_Click() 
'Add a new worksheet
 Worksheets. Add 
End Sub

Private Sub CommandButton2_Click() 
'Delete a worksheet
 Worksheets(1).Delete
End Sub

Example 20.6

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 20.7

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

---

Example 20.8

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

Example 20.9

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

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

Example 20.10

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