Listing tab names after a certain tab

[ardykav]

Hi, I have the below code which works well. I have 18 tabs in total and they change all the time apart from 2 or 3. However I now only want the list to start after the "template" tab which is the 4th tab. Can anyone tell me what I need to add to the below for this to work?

Sub Listalltabs()
 
Dim ws As Worksheet
Dim x As Integer
 
x = 1
 
Sheets("Tab list").Range("A:A").Clear
 
For Each ws In Worksheets
     Sheets("Tab list").Cells(x, 1) = ws.Name
     x = x + 1
Next ws
 
End Sub

 

[jolivanes]

Sub List_Sheet_Names()
Dim x As Long, i As Long
Sheets("Tab list").Range("A:A").Clear
x = 1
'For i = 5 To ThisWorkbook.Sheets.Count
' Or
 For i = Sheets("template").Index + 1 To ThisWorkbook.Sheets.Count    '<---- template sheet can be anywhere
        Sheets("Tab list").Cells(x, 1) = Sheets(i).Name
     x = x + 1
Next i
End Sub

 

[ardykav]

That is perfect thanks so much

 

[jolivanes]

Thank you for the feedback.
Good Luck

 

[ardykav]

Thank you for the feedback.
Good Luck

The above has worked superbly over the last few months, was just wondering if I wanted to extend this now to be after the template (have changed this tab name to simply "A") and before another tab (Tab named "Z") what would I need to put into the code?

Sub List_Sheet_Names()
Dim x As Long, i As Long
Sheets("list").Range("J:J").Clear
x = 1
'For i = 5 To ThisWorkbook.Sheets.Count
' Or
 For i = Sheets("A").Index + 1 To ThisWorkbook.Sheets.Count    '<---- template sheet can be anywhere
        Sheets("list").Cells(x, 10) = Sheets(i).Name
     x = x + 1
Next i
End Sub

 

[jolivanes]

Try this

For i = Sheets("template").Index + 1 To Sheets("Z").Index - 1    '<---- template sheet can be anywhere

If there is a chance of Sheet Z being next to Sheet template, you should have a message alerting you.
Maybe something like

 If Sheets("Z").Index = Sheets("template").Index + 1 Then MsgBox "No sheets between template and Z!":Exit Sub

 

[ardykav]

Worked a charm thanks so much

 

[jolivanes]

Thanks for letting us know.
Good luck