Help! Macro to select all sheet from AA to ZZ
 

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

Help! Macro to select all sheet from AA to ZZ
 

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

Sheets(shArr).Selec

Help! Macro to select all sheet from AA to ZZ
 

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

Help! Macro to select all sheet from AA to ZZ
 

Could have something to do with
ThisWorkbook.Sheets
try
For Each sh In .Sheets
or
For Each sh In activeworkbook.Sheets

--
Don Guillett
SalesAid Software

"Elaine Roden" wrote in message
...
Why is it that every time I type a reply to you, something new occurs to
me to try right after I click send?

I just copied the macro into the particular worksheet VB and it works, it
must be something to do with the fact that I was storing it in the personal
macro library instead of the worksheet macro library.

Any Ideas?

Elaine

Help! Macro to select all sheet from AA to ZZ
 

And just add to Don's excellent detective work:

ThisWorkbook refers to the workbook containing the code. If you put the code in
your personal.xls workbook, then those sheets would have to be that personal.xls
workbook.

By suggesting Activeworkbook, Don says to run the code against the, er,
activeworkbook.

(now that last sentence was quite helpful, huh?)



Elaine Roden wrote:

Why is it that every time I type a reply to you, something new occurs to me to try right after I click send?

I just copied the macro into the particular worksheet VB and it works, it must be something to do with the fact that I was storing it in the personal macro library instead of the worksheet macro library.

Any Ideas?

Elaine

--

Dave Peterson