Try this

Sub GroupSheets()

Dim sh As Object
Dim shArr() As String
Dim FirstSheet As Object
Dim LastSheet As Object

Set FirstSheet = Sheets("Sheet2")
Set LastSheet = Sheets("Sheet3")

ReDim shArr(FirstSheet.Index To LastSheet.Index)

For Each sh In ThisWorkbook.Sheets
If sh.Index = FirstSheet.Index And sh.Index <= LastSheet.Index Then
shArr(sh.Index) = sh.Name
End If
Next sh

Sheets(shArr).Select

End Sub

--
Post byDick Kusleika


--
Don Guillett
SalesAid Software

"Elaine Roden" wrote in message
...
How do I write a macro in Excel 2000 that selects all the sheets from
sheet aa to sheet zz. To explain what I need to do, our business is very
seasonal (about 40 year-round and 40 employees that come and go in spring
and fall) and when our employees are laid off, we move their sheets out of
the current section (aa-zz) and only print and update the emplyees that are
currently working. I would like to be able to move sheets in and out of this
group, but have the macro automatically select whatever sheets I have placed
between aa & zz. I am sort of new to Excel. I used to do all my programming
in a different spreadsheet, but the latest versions of it are terribly
unstable and I am converting everything to excel which is much more stable,
but it is a learning curve. (PS. aa and zz are names I gave to the dividing
sheets)

Thanks so much in advance. Your newsgroup is great and I have been able to
find lots of the answers that I need in here already!

Elaine

When I go into debug, the highlighed error out of range error is on the last line

Sheets(shArr).Selec

Did you change these lines:

Set FirstSheet = Sheets("aa")
Set LastSheet = Sheets("zz")

If you did, then you'll want to post the code you used. (Don's code worked fine
for me.)

Did you know that you can do the same thing manually by clicking on the AA
worksheet tab, then shift-clicking on the ZZ worksheet tab?



Elaine Roden wrote:

When I go into debug, the highlighed error out of range error is on the last line

Sheets(shArr).Select

--

Dave Peterson