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
 

StillUnderstanding

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

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you see post10?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
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