Excel VBA select multiple worksheets

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

Excel Assign Page Number 01

 

Method 2. Right click on one of the sheet, and then click Select All Sheets

Excel select multiple worksheets 01

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

Excel VBA select multiple worksheets 01

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *