How can I get a list of all the sheets that are in a file on one sheet?

Question

Is there a way to find the names of all the sheets as a list?

I can find the sheet name of the sheet where the formula is placed in via the following formula:

=RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-SEARCH("]";CELL("filename";A1);1))

This works for the sheet the formula is placed in. How can I get a list of all the sheets that are in a file on one sheet (let's say in cell A1:A5 if I have 5 sheets)?

I would like to make it so when someone changes a sheet name the macro keeps working.

score 3 · Answer 1 · answered Dec 10 '18 at 09:51

3

btw, in vba you can refer to worksheets by name or by object. See below, if you use the first method of referencing your worksheets it will always work with any name.

answered Dec 10 '18 at 09:51

SNicolaou

550
1
3
15

1

Yup, this is called a sheet’s `CodeName`, and it doesn’t change when users change a sheet’s `Name`. Here is more info for referencing a sheet with it https://stackoverflow.com/a/41481428/2727437 – Marcucciboy2 Dec 10 '18 at 15:36
3

To add to this - If a user copies a Sheet with a `CodeName` and you want your (for example) `Workbook_SheetChange()` to work against it, you can just check `Left(Sh.CodeName,8)="abcdefgh"`: The `CodeName` is copied and postfixed with an auto incrementing integer. I.e. copying "abcdefgh" would lead to a next sheet with codename "abcdefgh1". This is by far the most robust way to deal with multiple "sheet types" in an Excel VBA program. – Rik Sportel Dec 10 '18 at 15:57

score 1 · Answer 2 · answered Dec 10 '18 at 10:43

I would keep a very hidden sheet with the formula you used referencing each sheet.

When the Workbook_NewSheet event fires a formula pointing to the new sheet is created:

Create a sheet and give it the Code Name of shtNames.
- Give the sheet a tab name of SheetNames.
- In cell A1 of shtNames add a heading (I just used "Sheet List").
- In Properties for the sheet change Visible to 2 - xlSheetVeryHidden.
  You can only do this if there at least one visible sheet left.
Add this code to the ThisWorkbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    With shtNames
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Formula = _
            "=RIGHT(CELL(""filename"",'" & Sh.Name & "'!$A$1), " & _
            "LEN(CELL(""filename"",'" & Sh.Name & "'!$A$1))-" & _
            "FIND(""]"",CELL(""filename"",'" & Sh.Name & "'!$A$1),1))"
    End With

End Sub

Create a named range in the Name Manager:

I called it SheetList.
Use this formula:
=SheetNames!$A$2:INDEX(SheetNames!$A:$A,COUNTA(SheetNames!$A:$A))

You can then use SheetList as the source for Data Validation lists and list controls.

Two potential problems I haven't looked at yet are rearranging the sheets and deleting the sheets.

so when someone changes a sheetname the macro keeps working

As @SNicolaou said though - use the sheet code name which the user can't change and your code will carry on working no matter the sheet tab name.

EvR · Answer 3 · 2018-12-10T12:06:35.300

0

Make a defined name(formulas, name manager) : named: YourSheetNames in the field refersto you place:

=IF(NOW()>0,REPLACE(GET.WORKBOOK(1),1;FIND("]",GET.WORKBOOK(1)),""))

In your sheet you place in A1:A5:

=INDEX(YourSheetNames,ROW())

this wil give you (as long as calculation is set to xlautomatic) an actual list

edited Dec 10 '18 at 12:06

answered Dec 10 '18 at 09:28

EvR

3,418
2
13
23

1

This one makes a list of all the sheetnames but there is no link if the sheet names are changed. I would like to keep a link because I need that for my file. Thank you for your help, if you know another way tell me! – Mischa Urlings Dec 10 '18 at 09:50
Changed my answer, for a dynamic solution – EvR Dec 10 '18 at 12:07

LS_ᴅᴇᴠ · Answer 4 · 2018-12-10T10:59:14.390

0

a VBA function like:

Function SheetName(ByVal Index As Long, Optional ByVal Book as Range) as String
    Application.Volatile
    If Book Is Nothing Then Set Book = Application.Caller
    SheetName=Book.Worksheet.Parent.Sheets(Index).Name
End Function

would return sheet names by index, like an Excel formula. Example:

=SheetName(1) 'returns "Sheet1"
=SheetName(3) 'returns "Sheet3"

Using optional range in another book, you can get other book sheet names:

=SheetName(1, [Some other book.xls]Sheet1!A1) 'returns "Sheet1"
=SheetName(2, [Some other book.xls]Sheet1!A1) 'returns "Sheet2"

edited Dec 10 '18 at 10:59

answered Dec 10 '18 at 09:32

LS_ᴅᴇᴠ

10,823
1
23
46

1

As I understand this function will correctly give sheet name in the cell but if change is made by the user in sheet name, it will not get dynamically reflected in the cell. However if you enter the function again in the cell it will give the changed name of the sheet. OP desires something dynamic. – skkakkar Dec 10 '18 at 09:47
2

This is only related to Excel recalculation algorithm. As recalculation occurs (cells changes, book opens, manual calculation, ...) cell gets update. Anyway, added `Application.Volatile` which notifies Excel that this function return is willing to change. – LS_ᴅᴇᴠ Dec 10 '18 at 10:58
My understanding in this matter is enhanced by your clarification. Thanks a lot. – skkakkar Dec 10 '18 at 11:30

score 0 · Answer 5 · answered Dec 10 '18 at 10:19

@Mischa Urlings with the below code you get as a message in message box the following:

Sheet Name

Sheet Position

Option Explicit

Sub test()

Dim ws As Worksheet
Dim str As String

For Each ws In ThisWorkbook.Worksheets
    str = str & vbNewLine & "Sheet named " & ws.Name & " located in position " & ws.Index & "."
Next

'Get the names in a list in message box
MsgBox str

End Sub

score 0 · Answer 6 · answered Dec 08 '22 at 06:03

The following VBA macro function returns all worksheets names as an array:

Function GetWorksheets() As Variant
    
    Dim ws As Worksheet
    Dim x As Integer
    Dim WSArray As Variant
    ReDim WSArray(1 To Worksheets.Count)

    x = 1
    For Each ws In Worksheets
        'Sheets("Sheet1").Cells(x, 1) = ws.Name
        WSArray(x) = ws.Name
        x = x + 1
    Next ws
    
    'Output Array
    GetWorksheets = WSArray
End Function

After added to VBA Module, you can call it anywhere in a Workbook the same way you call a regular Excel Formula.

This VBA Function can be used as a more secure alternative to the following Excel 4.0 (XLM) macro:

=REPLACE(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

How can I get a list of all the sheets that are in a file on one sheet?

6 Answers6