VBA Select all sheets in between

[domtrump]

I have a workbook in which I need to select multiple sheets using VBA. The start sheet always has the same name and the end sheet always has the same name. However, there could be from 1 to 12 sheets in between with varying names (as well as some sheets after the end sheet or before the start sheet).

How can I select all the sheets in between start and end (including start and end) no matter how many are in the middle?

Thanks.

 

[VBA Geek]

Sheets(Evaluate("TRANSPOSE(ROW(" & Sheets([SIZE=3][COLOR=#ff0000][B]"Start"[/B][/COLOR][/SIZE]).Index + 1 & ":" & Sheets([SIZE=3][COLOR=#ff0000][B]"End"[/B][/COLOR][/SIZE]).Index - 1 & "))")).Select

Change the name of the sheets in bold

 

[RatExcel]

Sub Select_Middle_Sheets()

If Sheets.Count < 3 Then Exit Sub

Dim myArr() As String
ReDim myArr(Sheets.Count - 3)

For i = 2 To Sheets.Count - 1
    myArr(j) = Sheets(i).Name
    j = j + 1
Next i

Sheets(myArr).Select
Sheets(2).Activate
    
End Sub

 

[Rick Rothstein]

One more way...

Sub SelectSheetsBetweenStartAndEnd()
  Dim X As Long
  ActiveSheet.Select
  For X = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    Sheets(X).Select False
  Next
End Sub

 

[domtrump]

One more way...

Sub SelectSheetsBetweenStartAndEnd()
  Dim X As Long
  ActiveSheet.Select
  For X = Sheets("Start").Index + 1 To Sheets("End").Index - 1
    Sheets(X).Select False
  Next
End Sub

That got me close. I made the mods below to capture both the start and end sheets and make sure it didn't matter which sheet the user was on when the macro was run. Thanks for the help!

Sub SelectSheetsBetweenStartAndEnd()
  Dim X As Long
  Sheets("Start").Select
  For X = Sheets("Start").Index + 1 To Sheets("End").Index
    Sheets(X).Select False
  Next
End Sub