VBA to Dynamically list Worksheet Names

[MarkCBB]

Hi there,

I am looking for some VBA that can dynamically list all the names of the worksheets in a workbook (In Column A).
i.e. If I add a worksheet the name appears, if i edit the name the name is edited on the list and if I delete a worksheet the names is removed.

It doesn't need to be in order.

 

[Andrew Poulsom]

Maybe this will help you:

http://www.dailydoseofexcel.com/archives/2005/05/12/non-vba-table-of-contents/

 

[JoeMo]

Add a sheet named "Index" to your workbook and place this code in a module for this sheet (right-click sheet "Index" tab, select View Code and paste the code below into white space in the VB editor window). This will provide a dynamic list with hyperlinks to each sheet. The return links will go in cell A1 on each sheet (change code to use another range).

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim n As Integer
Dim calcState As Long, scrUpdateState As Long

calcState = Application.Calculation
Application.Calculation = xlCalculationManual
scrUpdateState = Application.ScreenUpdating
Application.ScreenUpdating = False

n = 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
            n = n + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                     .Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With
                
                Me.Hyperlinks.Add anchor:=Me.Cells(n, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet
    
Application.Calculation = calcState
Application.ScreenUpdating = scrUpdateState
End Sub

 

[MarkCBB]

Thanks Andrew.

 

[Jonmo1]

Nice!!

Maybe this will help you:

http://www.dailydoseofexcel.com/archives/2005/05/12/non-vba-table-of-contents/

 

[MarkCBB]

O WOW!!! Thanks JoeMo,

Just what I what looking for, thank you thank you thank you!!!

 

[ExcelKnut]

The above code works great! I would like to get a list of names for only visible sheets. How would the code above be adjusted to accomplish this?

 

[JoeMo]

The above code works great! I would like to get a list of names for only visible sheets. How would the code above be adjusted to accomplish this?

Change this line:

If wSheet.Name <> Me.Name Then

to this:

If wSheet.Name <> Me.Name And wSheet.Visible = xlSheetVisible Then

 

[Smitty]

Zack Barresse also created a nice Table of Contents builder that I wrote up here.

 

[erogalski]

Hi, is there a way to edit this VBA to only show the list of worksheets between two specific worksheetssheets? For example, I have an excel file with 4 worksheets at the beginning that are different reports, then I have a worksheet named "Start", then there are about 40 worksheets with various names, then there is a worksheet named "End." I would like the list to update with only the worksheets that fall between "Start" and "End"

Any ideas?

Thank you!

Ed