Consolidate data from several sheets between two “bookends” into one sheet

[stefk]

I'm looking to tweak some code I already have that consolidates data from multiple sheets into a single, master sheet.
Currently the VBA selects all sheets that starts with the prefix "A-", copies select cells and pastes them into the consolidation sheet.
The change I would like to make is rather than select sheets starting with "A-", instead select all sheets between two sheets. For Simplicity, let's call these "StartSheet" and "EndSheet"
Illustratively it would look something like this:
< startsheet > < analysis1 > < analysis2 > < analysis3 > < endsheet >
Reason being, I, or someone else, can then just drop the analysis sheets between the bookends without risk of lookups and naming conventions.
I've tried a few ways to attempt to fuse my existing code with other examples I have found online but none seem to work. Help greatly appreciated!!
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">
Sub compile()

SelectSheets "A-", ThisWorkbook

'Some other bits and pieces here
End Sub


Sub SelectSheets(sht As String, Optional wbk As Workbook)

Dim wks As Worksheet
Dim ArrWks() As String
Dim I As Long

If wbk Is Nothing Then Set wbk = ActiveWorkbook

ReDim ArrWks(0 To Worksheets.Count - 1)
For Each wks In Worksheets
If InStr(1, wks.Name, sht) > 0 Then
ArrWks(I) = wks.Name
I = I + 1
End If
Next wks
ReDim Preserve ArrWks(I - 1)
Sheets(ArrWks).Select

Application.ScreenUpdating = False

For Each ws In Sheets(ArrWks)
ws.Range("A23:CU27,A35:CU54,A56:CU58,A62:CU71,A74:CU84").Copy
Worksheets("consol").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)


Next ws


Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub</code>

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub stefk()
   Dim i As Long
   
   For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
      Sheets(i).Range("A23:CU27,A35:CU54,A56:CU58,A62:CU71,A74:CU84").Copy
      Worksheets("consol").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   Next i
   Application.CutCopyMode = False
End Sub

 

[stefk]

Hi & welcome to MrExcel.
How about

Sub stefk()
   Dim i As Long
   
   For i = Sheets("Start").Index + 1 To Sheets("End").Index - 1
      Sheets(i).Range("A23:CU27,A35:CU54,A56:CU58,A62:CU71,A74:CU84").Copy
      Worksheets("consol").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
   Next i
   Application.CutCopyMode = False
End Sub

worked like a charm! thank you

 

[Fluff]

You're welcome & thanks for the feedback