0

I have been given a macro-enabled workbook with well over 100 sheets that I need to understand and document. I have a piece of code that lists the index of the sheet and the name of the sheet.

Sub list_sheets()
    Dim WS_Count As Integer
         Dim i As Integer
         WS_Count = ActiveWorkbook.Worksheets.count
         For i = 1 To WS_Count
            Debug.Print i, ActiveWorkbook.Worksheets(i).name, ActiveWorkbook.Worksheets(i).SheetName
         Next i
End Sub

This produces output as follows:

 1            ReadMe
 2            TableOfContents
 3            DATA
 ...

BUT on the Microsoft Excel Object panels it has names like

"Sheet1 (DATA)"
"Sheet2 (Manifest)"

I would expect the number on the sheet name to match the index, but that's okay. If I can just get to the other name.

Note that if I check the properties for the sheet, then this other name is listed under the property "(Name)" as opposed to "Name", i.e. distinguished by enclosing parentheses. How can I get at that "(Name)" property in addition to the "Name" property?

elbillaf
  • 1,952
  • 10
  • 37
  • 73
  • 1
    A `Worksheet` doesn't have a `SheetName` property. You're looking for [`CodeName`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.codename). The index (position) and name of a sheet can easily change. The codename, much less so. – BigBen May 04 '21 at 00:43
  • That's it. Thanks – elbillaf May 04 '21 at 00:48

0 Answers0