Finding Worksheet Names Located Between Two Worksheets?

[Duvnjak]

Does anyone know of an effective way in VBA to grab all the worksheet names located within two different worksheets?

i.e. Start,1,2,3,4,5,End

I want to grab the names of 1,2,3,4,5.

Thanks in advance!

 

[rdw72777]

This code should do what you want. its a bit meandering because I can't think of a cleaner code to do it.

This code assumes that (1) you have a named range called "startsheet" and "endsheet" where you enter the Start Sheet and End Sheet names you want to find sheets betwee and (2) that your output will be put on a sheet named "sheet1" beginning in Cell "A1".

Sub FindSheetNames()

Dim startsheet As Variant
Dim endsheet As Variant
Dim startsheetnum As Long
Dim endsheetnum As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim SheetCount As Long

startsheet = Range("startsheet").Value
endsheet = Range("endsheet").Value
SheetCount = ActiveWorkbook.Sheets.Count

For i = 1 To SheetCount

    If Sheets(i).Name = startsheet Then
    
        startsheetnum = i
    
    ElseIf Sheets(i).Name = endsheet Then
    
        endsheetnum = i
    
    End If

Next i

k = 1

For j = startsheetnum + 1 To endsheetnum - 1

    Sheets("Sheet1").Select
    Cells(k, 1) = Sheets(j).Name
    
    k = k + 1

Next j

End Sub

 

[paipimenta]

Voyla... (voila)

Swap "Start" and "End" with the name of your start and end sheet.
Stores sheet names in wSheetNames() string array variable to use with VBA code.

So...
wSheetNames(0) = "1"
wSheetNames(1) = "2" etc.

UBound(wSheetNames) = total # of sheets

Sub GimmeSheetNames()
Dim wSheetNames() As String
Dim betweenTheSheets As Integer
Dim pullNames As Boolean
 
betweenTheSheets = -1
 
For Each wSheet In ThisWorkbook.Worksheets
If wSheet.Name = "End" Then
pullNames = False
End If
 
If pullNames Then
betweenTheSheets = betweenTheSheets + 1
ReDim Preserve wSheetNames(0 To betweenTheSheets)
wSheetNames(betweenTheSheets) = wSheet.Name
' MsgBox Prompt:="wSheetNames(" & betweenTheSheets & ") = " & wSheetNames(betweenTheSheets)
End If
 
If wSheet.Name = "Start" Then
pullNames = True
End If
Next wSheet
End Sub

Uncomment MsgBox prompt to check name selection

 

[Duvnjak]

Thanks! It's more or less what I figured out moments ago!

 

[Norie]

Try this.

Option Explicit
 
Sub GetWSNames()
Dim ws As Worksheet
Dim arrWSnms()
Dim I As Long
 
    For Each ws In Worksheets

        Select Case ws.Name
            Case "Start", "End"
                ' do nothing
            Case Else
                ReDim Preserve arrWSnms(I)
                arrWSnms(I) = ws.Name
                I = I + 1
        End Select
                
    Next ws
    
    MsgBox Join(arrWSnms, ",")
    
End Sub