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

 

[StillUnderstanding]

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

Here is the full code as pasted into the book

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 And wSheet.Name <> "Admin" And wSheet.Name <> "Sheet6" 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]

And?

 

[StillUnderstanding]

And?

And still not working

 

[StillUnderstanding]

And still not working

It really is an odd one. I used google to see if I could find the answer and I cant find anything. It as if it has never been tried before.

 

[Fluff]

Did you see post10?

 

[StillUnderstanding]

Did you see post10?

Yes and I checked the names etc and they are all correct.

 

[Fluff]

Can you post the outcome of that macro?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[StillUnderstanding]

Can you post the outcome of that macro?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I cant install things on my laptop so decided to try from scratch on a new workbook, I have it working now and discovered my very silly mistake.... I put it into a Module and not directly into the Index page.

Now it's in the right place it is working with the following code:-

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 And wSheet.Name <> "Sheet3" 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]

Glad you sorted it & thanks for the feedback.