Excel VBA - Selecting Worksheets

When writing VBA, there are a number of occasions when you will have to select a single or group of worksheets. In this blog, we will explore some of the ways we can do this.

Selecting a single Worksheet
Selecting all Worksheets
Selecting the last sheet

Selecting a single Worksheet

The first method of selecting a sheet, uses the sheets name. The example below would select the sheet called Sheet 1.

Sub SelectSingleSheet()
    
       Sheets("Sheet1").Select
    
End Sub

The second method uses the sheets position in the workbook. The example below would select the first sheet in the workbook.

Sub SelectSingleSheet2()
    
    Sheets("1").Select
    
End Sub

Both methods have negatives, and so you need to be aware, that if selecting a sheet with it’s name, the name must not be changed. If selecting a sheet by position, then the sheet must not be moved. 

Selecting all Worksheets

Selecting all the sheets in the workbook can be done using the following code:

Sub SelectAllSheets()
    
    Sheets.Select
    
End Sub

Some examples of why you may need to select all the worksheets at once include:

• Print Setup for the entire workbook
• Zoom in/out in all worksheets
• Colour all of the Worksheet tabs
• To run a Macro/VBA on all Worksheets

etc..

Selecting the Last Worksheet in a workbook

You are able to select the last sheet in a workbook, by using the following code:

Sub LastWorksheet()

    Sheets(Sheets.Count).Select
    
End Sub

By using sheets.count, you are counting how many sheets there are in the workbook. For example, If there were 3 sheets in your workbook, then you would be selecting the third sheet (from the left), and so the last sheet.

To learn more about Excel VBA, join us on our Introduction to Excel Macros/VBA Course.