Code to select all worksheets (variable number of sheets)

[OssieMac]

I want to do the following but with a variable number of worksheets.

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select

or the following.

Sheets(Array(1, 2, 3)).Select

I found similar code to the following from this forum and it was checked as
an answer but I am not having any success. See the comment in the code for
the error.

Sub SelectMultipleSheets()

Dim intShtsCount As Integer
Dim shtArray()
Dim i As Integer

intShtsCount = ActiveWorkbook.Sheets.Count
ReDim shtArray(intShtsCount)
For i = 0 To intShtsCount - 1
shtArray(i) = Sheets(i + 1).Name
Next i

'*******************************
'Following line returns runtime error '9' Subscript out of range
'********************************
Sheets(shtArray).Select

End Sub

All help appreciated.

 

[Chip Pearson]

The problem you are having is that you are declaring the array with
one extra element, with the code

ReDim shtArray(intShtsCount)

Since your array is 0-based, the last element of the array will be
empty. E.g., with three sheets, you'll have

shtArray(0) = Sheet1
shtArray(1) = Sheet2
shtArray(2) = Sheet3
shtArray(3) = empty

Use code like

ReDim shtArray(intShtsCount - 1)

As a matter of general good coding practice, you should always specify
the lower bound of the array. E.g., instead of

ReDim shtArray(intShtsCount)

Use

ReDim shtArray(0 To intShtsCount - 1)

The reason is that the default lower bound of an array is either 0 or
1, depending on the existence of and value of the Option Base
directive at the top of the module:

Option Base 0 -> arrays begin at element 0 unless specified
Option Base 1 -> arrays begin at element 1 unless specified
no Option Base statement -> Option Base 0 assumed.

If you copy the code from one module to another, the lower bound of
the array may be changed from 0 to 1. Thus, always specify the lower
bound.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[OssieMac]

Thankyou Chip. I am very greatful for your help.
However, I am suitably embarrassed. Not only could I not see the error for
which I knew the answer, I didn't test UBound(shtArray) which would have
immediately indicated what the error was. I simply put a MsgBox in the
For/Next loop to ensure it was picking up all the sheets.