Conditional sheet names macro?

[gaftalik]

Hi All,

Any macro which can index all sheet names which are between two specified sheets ( ex: Sheet 6 and sheet 25) and in a desired range of cells?

I appreciate very much your help !

 

[mikerickson]

These routines can do that. You can either adjust the Destination Range and limiting sheet names in writeSheetsBetween.

Or select the cells where you want the names and enter the array formula =SheetsBetween("Sheet6","Sheet25")

Sub writeSheetsBetween()
    Dim outputArray As Variant
    Dim destinationRange As Range
    Set destinationRange = ThisWorkbook.Sheets("Sheet4").Range("a1")
    outputArray = SheetsBetween("Sheet1", "Sheet4")
    destinationRange.Resize(UBound(outputArray) - LBound(outputArray) + 1, 1) = Application.Transpose(outputArray)
End Sub

Function SheetsBetween(lowSheetName As String, highSheetName As String) As Variant
    Dim outRRay() As String, i As Long
    Dim lowSheetIndex As Long, highSheetIndex As Long

    lowSheetIndex = ThisWorkbook.Sheets(lowSheetName).Index
    highSheetIndex = ThisWorkbook.Sheets(highSheetName).Index
    If highSheetIndex < lowSheetIndex Then
        highSheetIndex = lowSheetIndex
        lowSheetIndex = ThisWorkbook.Sheets(highSheetName).Index
    End If

    If highSheetIndex <= lowSheetIndex + 1 Then
        ReDim outRRay(1 To 1)
        outRRay(1) = vbNullString
    Else
        ReDim outRRay(lowSheetIndex + 1 To highSheetIndex - 1)
        For i = lowSheetIndex + 1 To highSheetIndex - 1
            outRRay(i) = ThisWorkbook.Sheets(i).Name
        Next i
    End If

    SheetsBetween = outRRay
    If TypeName(Application.Caller) = "Range" Then
        If 1 < Application.Caller.Rows.Count Then
            SheetsBetween = Application.Transpose(outRRay)
        End If
    End If
End Function

Array formulas are entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[gaftalik]

Thank you so much ! works great !