XL2000: Sheets(Array).Select Statement Does Not Select All Sheets in the Array

When you programmatically select all of the sheets in an Excel workbook as an array, only the activated sheet gets selected.

Example

The following example illustrates this behavior.

1. Start Microsoft Excel, and create an Excel Wordbook with five sheets named Sheet5, Sheet4, Sheet1, Sheet2, and Sheet3 respectively.
   
   NOTE: To add worksheets, click Worksheet on the Insert menu.
2. Press ALT+F11 to start the Visual Basic editor.
3. On the Insert menu, click Module.
4. Type the following code in the module sheet:

   Sub test()
      Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Select
      Sheets("Sheet3").Activate
   End Sub
   						

5. Press ALT+F11 to switch to the Excel workbook.
6. Press ALT+F8 to open the macro list, click test under Macro name, and then click Run. Only Sheet 3 is selected.

This problem occurs because the sequence of the sheets named in the macro does not match the sequence of the sheets as they appear on the Excel workbook, and the sheet that is activated is not the first sheet in the array. This problem only occurs when you attempt to programmatically select all of the sheets in a workbook. When you programmatically select some the sheets in a workbook, the sequence of the worksheets is not important.

To resolve this problem, make sure that the sequence of the sheets in the macro matches the sequence of the sheet numbers on the Excel workbook, and ensure that the sheet that is activated is the first sheet in the array.

For example, if you change the code in the example macro in the "Symptoms" section to the following the macro works as expected. All sheets are selected and sheet 5 is activated.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.