How to copy a subset of data from a dynamic list?

[kungfugecko]

I have a workbook with multiple sheets, such as this:

and I am using a VBA code to create a dynamic index page which will list all new sheets added:

The 'First' and 'Last' sheets will always be present in this workbook, and all new sheets will be placed between these sheets. Is there a formula to create a list of all values (names in this case) that are present between 'First' and 'Last' on this dynamic list? so I would like to have a formula to output a list of just:

Abby Alkatraz
Billy Bob
Crazy Chris

Thanks in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

Suppose you wanted the list on the 'Index' sheet in column A starting at row 2, you could try this.

Sub ListSheets()
  Dim i As Long, k As Long
  
  For i = Sheets("First").Index + 1 To Sheets("Last").Index - 1
    Sheets("Index").Range("A2").Offset(k).Value = Sheets(i).Name
    k = k + 1
  Next i
End Sub

 

[kungfugecko]

Welcome to the MrExcel board!

Suppose you wanted the list on the 'Index' sheet in column A starting at row 2, you could try this.

Sub ListSheets()
  Dim i As Long, k As Long
 
  For i = Sheets("First").Index + 1 To Sheets("Last").Index - 1
    Sheets("Index").Range("A2").Offset(k).Value = Sheets(i).Name
    k = k + 1
  Next i
End Sub

Thank you so much for the help! I tried your solution but I think I am just not implementing it properly. I have applied this code to my subindex work sheet, but there is no output on the sheet, even after saving and Refresh All.

 

[kungfugecko]

Welcome to the MrExcel board!

Suppose you wanted the list on the 'Index' sheet in column A starting at row 2, you could try this.

Sub ListSheets()
  Dim i As Long, k As Long
 
  For i = Sheets("First").Index + 1 To Sheets("Last").Index - 1
    Sheets("Index").Range("A2").Offset(k).Value = Sheets(i).Name
    k = k + 1
  Next i
End Sub

I apologize I'm not sure how to edit my previous comment, but I wanted to let you know I did get it figured out, your solution worked brilliantly. Thank you!!!

 

[Peter_SSs]

You're welcome. Glad you got it resolved. Thanks for letting us know.