Is there a way to create a list of sheet names that will auto-populate when new sheets are added?

I'd like to create a drop down from this list to then use as a function parameter as the sheet to create a new sheet after.

CodePudding user response：

VBA seemed to be the solution. The followng will create a list of all sheet names and add them to a new sheet called AllSheets - if the sheet AllSheets doesn't exist it will create it first.

Sub ListSheets()
Dim i As Integer
Dim sh As Worksheet
Const txt = "AllSheets"

    If Not Evaluate("ISREF('" & txt & "'!A1)") Then
        Set sh = Worksheets.Add
        sh.Name = "AllSheets"
        sh.[A1] = "Workbook Sheets"
    End If

Set sh = Sheets("AllSheets")

    For i = 1 To Worksheets.Count
        sh.Cells(i   1, 1) = Sheets(i).Name
    Next i

End Sub

CodePudding user response：

To add to Automation Monkey's fine script the following does three additional things:

1. Forces the new sheet to be the first sheet.
2. Makes each entry a hyperlink to the referenced sheet.
3. Every time the AllSheets sheet is selected the list will regenerate. This will catch sheet adds, deletes, and renames.

Option Explicit

Sub ListSheets()
Dim i As Integer
Dim Sh As Worksheet
Const txt = "AllSheets"

    If Not Evaluate("ISREF('" & txt & "'!A1)") Then
       '*** Add as first sheet ***
        Set Sh = Worksheets.Add(Before:=ActiveWorkbook.Worksheets(1))
        Sh.Name = "AllSheets"
        Sh.[A1] = "Workbook Sheets"
    Else
       Range("A2:A300").ClearContents
    End If
    

Set Sh = Sheets("AllSheets")

    For i = 1 To Worksheets.Count
        Sh.Cells(i   1, 1) = Sheets(i).Name
        '*** Make entry a hyperlink to the sheet ***
        Sh.Hyperlinks.Add Anchor:=Cells(i   1, 1), Address:="", SubAddress:=(Sheets(i).Name) & _
                       "!A1", TextToDisplay:=Sheets(i).Name
    Next i

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  If (Sh.Name = "AllSheets") Then
    ListSheets
  End If
  
End Sub