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

StillUnderstanding

New Member
Joined
Jan 30, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
You haven't told it to ignore any sheets, other than the index sheet. :unsure:
 

StillUnderstanding

New Member
Joined
Jan 30, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
You haven't told it to ignore any sheets, other than the index sheet. :unsure:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Try it like
VBA Code:
If wSheet.Name <> Me.Name And wSheet.Name<> Admin.Name And wSheet.Name<> Sheet3.Name Then
 

StillUnderstanding

New Member
Joined
Jan 30, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Try it like
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
What are Admin & Sheet3?
Sheet names, sheet code names, or variables?
 

StillUnderstanding

New Member
Joined
Jan 30, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
If wSheet.name <> Me.name And wSheet.name <> "Admin" And wSheet.name <> "Sheet3" Then
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Make sure that the sheet names do not have any leading/trailing spaces & that they are spelt correctly, including capitalisation.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,349
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top