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