This Excel tutorial explains how to select multiple worksheets using Excel VBA Worksheet.Select Method and using Array.
You may also want to read:
Excel export Excel to PDF with and without VBA
Select multiple worksheets in Excel
In Excel worksheet, there are two ways to select multiple worksheets
Method 1. Hold down Ctrl and then left click the mouse on each worksheet tab
Method 2. Right click on one of the sheet, and then click Select All Sheets
One practical use of selecting multiple worksheets is to print selected worksheets.
Select multiple worksheets using Excel 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.
But if you really want to use VBA to physically select multiple worksheet, you can use Worksheet.Select Method
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
To select Sheet1 and Sheet2, use the False Property in Sheet2
Sheets("Sheet1").Select
Sheets("Sheet2").Select False
Select multiple worksheets using Array
To select Sheet1 and Sheet2, put the worksheet names in Array
Sheets(Array("Sheet1", "Sheet2")).Select
Alternatively, create an Array first and then define the array items
Dim myarray(1) As String myarray(0) = "Sheet1" myarray(1) = "Sheet2" Sheets(myarray()).Select
Example
Assume there are 5 worksheets in a workbook, from Sheet1 to Sheet 5 in order, the below example copies Sheet and and Sheet5 before Sheet1
Public Sub cpyWS()
Sheets(Array("Sheet2", "Sheet5")).Copy Before:=Sheets("Sheet1")
End Sub
