VBA to create an Index page that includes all tabs apart from some exceptions

[StillUnderstanding]

HI Everyone,

So I an trying to add an index page in a workbook that includes a list tabs in the workbook, the issue I am having is that it is listing all tabs in the book apart from the Index page.

What I am attempting to do is have the index show as normal but exclude any sheets that I have told it to ignore.

I would be grateful if anyone could help with this!


Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index

End With
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub

 

[Fluff]

You haven't told it to ignore any sheets, other than the index sheet.

 

[StillUnderstanding]

You haven't told it to ignore any sheets, other than the index sheet.

OOPS, I did this and it still included the 2 named pages Admin and Sheet3

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Or Admin.Name Or Sheet3.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index

End With
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub

 

[Fluff]

Try it like

If wSheet.Name <> Me.Name And wSheet.Name<> Admin.Name And wSheet.Name<> Sheet3.Name Then

 

[StillUnderstanding]

Try it like

If wSheet.Name <> Me.Name And wSheet.Name<> Admin.Name And wSheet.Name<> Sheet3.Name Then

Tried that also and that brings the full list back It's not excluding the Admin or Sheet3 from the index.

 

[Fluff]

What are Admin & Sheet3?
Sheet names, sheet code names, or variables?

 

[StillUnderstanding]

That's the name that the tabs are called. When I went into the VBA and looked at the Microsoft Excel Objects I could see Sheet5 (Admin) and so I tried both Sheet5 and also admin. No luck

 

[Fluff]

Ok, how about

If wSheet.name <> Me.name And wSheet.name <> "Admin" And wSheet.name <> "Sheet3" Then

 

[StillUnderstanding]

Ok, how about

If wSheet.name <> Me.name And wSheet.name <> "Admin" And wSheet.name <> "Sheet3" Then

No, that is also not working. hum

 

[Fluff]

Make sure that the sheet names do not have any leading/trailing spaces & that they are spelt correctly, including capitalisation.