Hell
What code would I use to select all sheets between sheet x and sheet y.
Thanks!

Carol

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Carol" wrote in message
...
Hello
What code would I use to select all sheets between sheet x and sheet y.
Thanks!

Thanks
Im getting out of range on Sheets(shArr).Select :-

----- Dick Kusleika wrote: ----

Caro

Try thi

Sub GroupSheets(

Dim sh As Objec
Dim shArr() As Strin
Dim FirstSheet As Objec
Dim LastSheet As Objec

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

ReDim shArr(FirstSheet.Index To LastSheet.Index

For Each sh In ThisWorkbook.Sheet
If sh.Index = FirstSheet.Index And sh.Index <= LastSheet.Index The
shArr(sh.Index) = sh.Nam
End I
Next s

Sheets(shArr).Selec

End Su

--
Dick Kusleik
MVP - Exce
Excel Blog - Daily Dose of Exce
www.dicks-blog.co

"Carol" wrote in messag
..
Hell
What code would I use to select all sheets between sheet x and sheet y
Thanks

Carol

This worked for me. If you still get a subscript out of range error, then
post the code as you have it and list the sheets in the order they appear in
your workbook.

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("Sheet4")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Carol" wrote in message
...
ALso I might add this should be non inclusive of sheet2 and sheet3...Index
sheet2 +1 Index sheet3 -1 Ors omething like that
Thanks again!

Your Code


Sub GroupSheets()

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

Set FirstSheet = Sheets("Start")
Set LastSheet = Sheets("End")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

My sheets in order
Start
214507
314492
314183
314482
314522
314456
189034
189037
205073
205074
216903
End

Im still getting "Select method of Sheets class Failed"

Thanks!


"Dick Kusleika" wrote in message
...
Carol

This worked for me. If you still get a subscript out of range error, then
post the code as you have it and list the sheets in the order they appear
in
your workbook.

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("Sheet4")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Carol" wrote in message
...
ALso I might add this should be non inclusive of sheet2 and
sheet3...Index
sheet2 +1 Index sheet3 -1 Ors omething like that
Thanks again!

Steve

I set up a workbook with all of the sheets you listed. I then copied and
pasted the code into a standard module. When I ran the code, it selected
all the sheets between Start and End.

I'm at a loss as to why it won't work for you. I can send you my workbook
or you can send me yours and maybe we can see what the difference is.

What version of Excel are you using? It shouldn't matter, but I'm grasping
at straws at this point.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Steve" <sailor4life61@hotmaildotcom wrote in message
...
Your Code


Sub GroupSheets()

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

Set FirstSheet = Sheets("Start")
Set LastSheet = Sheets("End")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

My sheets in order
Start
214507
314492
314183
314482
314522
314456
189034
189037
205073
205074
216903
End

Im still getting "Select method of Sheets class Failed"

Thanks!


"Dick Kusleika" wrote in message
...
Carol

This worked for me. If you still get a subscript out of range error,
then
post the code as you have it and list the sheets in the order they
appear
in
your workbook.

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("Sheet4")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Carol" wrote in message
...
ALso I might add this should be non inclusive of sheet2 and
sheet3...Index
sheet2 +1 Index sheet3 -1 Ors omething like that
Thanks again!

Not sure what we did but we got it working----Thanks!


"Dick Kusleika" wrote in message
...
Steve

I set up a workbook with all of the sheets you listed. I then copied and
pasted the code into a standard module. When I ran the code, it selected
all the sheets between Start and End.

I'm at a loss as to why it won't work for you. I can send you my workbook
or you can send me yours and maybe we can see what the difference is.

What version of Excel are you using? It shouldn't matter, but I'm
grasping
at straws at this point.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Steve" <sailor4life61@hotmaildotcom wrote in message
...
Your Code


Sub GroupSheets()

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

Set FirstSheet = Sheets("Start")
Set LastSheet = Sheets("End")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

My sheets in order
Start
214507
314492
314183
314482
314522
314456
189034
189037
205073
205074
216903
End

Im still getting "Select method of Sheets class Failed"

Thanks!


"Dick Kusleika" wrote in
message
...
Carol

This worked for me. If you still get a subscript out of range error,
then
post the code as you have it and list the sheets in the order they
appear
in
your workbook.

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("Sheet4")

If FirstSheet.Index LastSheet.Index - 2 Then
MsgBox "First sheet must be less than Last sheet"
Exit Sub
End If


ReDim shArr((FirstSheet.Index + 1) To (LastSheet.Index - 1))

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

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Carol" wrote in message
...
ALso I might add this should be non inclusive of sheet2 and
sheet3...Index
sheet2 +1 Index sheet3 -1 Ors omething like that
Thanks again!