This Excel VBA tutorial explains how to use Worksheet.Select Method to select a single worksheet or multiple worksheets.
Select worksheets in Excel
When you click on a worksheet tab, the worksheet is highlighted.
To select multiple worksheets, you can hold down Ctrl and then left click the mouse on each worksheet tab.
To select all worksheets at once, right click on one of the sheet, and then click on Select All Sheets
One practical use of selecting multiple worksheets is to print selected worksheets.
In this tutorial, I will explain how to perform the same tasks in the above scenarios using Excel VBA Worksheet.Select Method.
Excel VBA Worksheet.Select Method
In Excel VBA, it is not necessary to select worksheets in order to run a Macro on selected worksheets, because you can use VBA to loop through worksheets with specific name.
Syntax of Worksheet.Select Method
Worksheet.Select(Replace)
Name | Required/Optional | Data Type | Description |
Replace | Optional | Variant | (used only with sheets). True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object. |
Example 1 – Select a single worksheet
To select Sheet1 only
Sheets("Sheet1").Select
Example 2 – Select multiple worksheets
To select Sheet1 and Sheet2, use the False Property in Sheet2
you can also add the False argument for the first Worksheet
Sheets("Sheet1").Select False Sheets("Sheet2").Select False
Example 3 – Select all worksheets in the workbook
The below example selects all worksheets in current workbook
Public Sub selectAllWS() For Each ws In ThisWorkbook.Sheets ws.Select flase Next End Sub
After you have selected all worksheets, you can deselect them by selecting anyone of the worksheet. To avoid specifying which worksheet, I use ActiveSheet in the below example.
In multiple selection, ActiveSheet refers to the first selected worksheet.
Public Sub deselectWS() ActiveSheet.Select End Sub
You can also select multiple worksheets using Array.
Outbound References
https://msdn.microsoft.com/en-us/library/office/ff194988.aspx