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.

 

[ExcelKnut]

JoeMo,

Please accept my belated thanks. I appreciate(d) your help!!!

Jeff

 

[JoeMo]

JoeMo,

Please accept my belated thanks. I appreciate(d) your help!!!

Jeff

You are welcome - thanks for the reply.

 

[JoeMo]

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

Untested, but I think this revision will do what you want.

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 And wSheet.Index > Sheets("Start").Index And _
            wSheet.Index < Sheets("End").Index 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

 

[erogalski]

Wow, works perfectly! I don't know how you know how to do this stuff but it is amazing. Thank you so much!

 

[JoeMo]

Wow, works perfectly! I don't know how you know how to do this stuff but it is amazing. Thank you so much!

You are welcome - thanks for the reply.

 

[BVavra]

I am trying to adapt this code for a workbook I am working on. It works perfectly until I try to add my hidden template "Master" then I get a runtime error. Here is the code:

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) = "COLLECTION"
        .Cells(1, 1).Name = "Collection"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And wSheet.Visible = xlSheetVisible Then
            n = n + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Collection", TextToDisplay:="Back to Collection"
                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

This is the portion of the code that gets flagged when I go to debug.

.Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Collection", TextToDisplay:="Back to Collection"

Thanks for any help. I've been working on this for a week now.

 

[JoeMo]

I am trying to adapt this code for a workbook I am working on. It works perfectly until I try to add my hidden template "Master" then I get a runtime error. Here is the code:

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) = "COLLECTION"
        .Cells(1, 1).Name = "Collection"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name And [B][COLOR=#ff0000]wSheet.Visible = xlSheetVisible[/COLOR][/B] Then
            n = n + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Collection", TextToDisplay:="Back to Collection"
                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

This is the portion of the code that gets flagged when I go to debug.

.Hyperlinks.Add anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Collection", TextToDisplay:="Back to Collection"

Thanks for any help. I've been working on this for a week now.

Not sure I understand. is "Master" a worksheet in the workbook the code is in? If yes, and Master is hidden, I don't see how you ever get to the error line with the bit I highlighted in red there.

 

[BVavra]

Not sure I understand. is "Master" a worksheet in the workbook the code is in? If yes, and Master is hidden, I don't see how you ever get to the error line with the bit I highlighted in red there.

The "Master" is a hidden worksheet in the same workbook. I have a separate macro that unhides the MASTER, makes a copy of it, renames it to whatever I input then rehides it. I only want to have a list of the visible worksheets.


The worksheet order (if it matters) is, Master, Notes, Drop Down, Instructions, Collection, new sheet1, new sheet2, etc. The sheets in blue text are hidden.

 

[JoeMo]

The "Master" is a hidden worksheet in the same workbook. I have a separate macro that unhides the MASTER, makes a copy of it, renames it to whatever I input then rehides it. I only want to have a list of the visible worksheets.


The worksheet order (if it matters) is, Master, Notes, Drop Down, Instructions, Collection, new sheet1, new sheet2, etc. The sheets in blue text are hidden.

With the code you posted the hidden sheets should not be listed. If you want them listed, then remove the bit I highlighted in red in post #17.

 

[BVavra]

With the code you posted the hidden sheets should not be listed. If you want them listed, then remove the bit I highlighted in red in post #17.

The hidden sheets aren't listed but neither are any of the other ones and I get a runtime error about the hyperlink range.

[COLOR=#333333][FONT=monospace][I].Hyperlinks.Add anchor:=.Range("A1"), Address:="", _SubAddress:="Collection", TextToDisplay:="Back to Collection"[/I][/FONT][/COLOR]

If I use the code in a blank workbook and move over the other sheets one by one everything works great until I add the Master sheet.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">.Hyperlinks.Add anchor:=.Range("A1"), Address:="", _ SubAddress:="Collection", TextToDisplay:="Back to Collection"</body>