VBA to list worksheet names apart from

[nicic]

Hi there,
I'm hoping someone can help. I'm creating a 'quick look' page, and need the worksheetnames in the first column to appear automatically, and change as sheets are added etc.
There will be 2-3 sheets I don't want listed.

I've played around with some vba code, I have a nice one from here to create a menu of all the sheets, which is hyperlinked. But my brain is struggling today, and I can't fathom how to list the worksheet names without a hyperlink, and in the cell I want (B3).

So basically, how can I autpoulate cells with worksheet names, that change dynamically, and stop it listing a set 3 sheets (names will be static). Oh and preferably sort a-z?

Thanks

 

[Fluff]

Are the 3 sheets to be excluded the 1st and/or the last 3 sheets?

 

[nicic]

Are the 3 sheets to be excluded the 1st and/or the last 3 sheets?

They are the first 3

 

[Fluff]

Ok, on the formula tab click on name manager, new, Give it a name (I called it ShtNames) and in the refers to box put

=TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]"))

Then in B3 use

=SORT(DROP(ShtNames,3))

 

[nicic]

Thank so much I will try it


I wonder if you can also help with this?
I got the following code from here and it works wonderfully, but I won't to remove the back to index on each page, and be able to site the list on cell B3?
Thanks

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) = "Menu"
        .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

 

[Fluff]

Why do you want to use VBA that will not update the list of sheet names automatically, which the formula will?

 

[nicic]

Why do you want to use VBA that will not update the list of sheet names automatically, which the formula will?

The vba does update automatically, it's linked to the worksheet, so everytime you go onto the page it updates. It just worked so well, and I was frustrated I couldn't figure how to change it. The list was actually for something else, but I think I may use it for the menu too now - Thanks
I don't suppose there is code that can be added to turn then into hyperlinks?

 

[Dave Patton]

T202309a.xlsm
	A	B
1
2		1d
3		1e
4		1f
5		1g
6		zList
1d

Cell Formulas
Range	Formula
B2:B6	B2	=ShtNames()
Dynamic array formulas.

You can combine Fluff's 2 lines and have your own Function.
With Name Manager name the function say ShtList
Value =LAMBDA(SORT(DROP(TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]")),3)))

 

[Fluff]

That code does turn them into hyperlinks, which you originally said you didn't want

 

[Fluff]

No need to use Lambda, you can just use this in the name manager

=SORT(DROP(TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]")),3))