Code to select all worksheets (variable number of sheets)

  • Thread starter Thread starter OssieMac
  • Start date Start date
O

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.
 
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)
 
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.
 
Back
Top