Sheet Names

It is very simple to retrieve sheet names in VBA.  They are stored in two collection objects in the ActiveWorkbook object: the Sheets collection and the Worksheets collection.  The Sheets collection contains both worksheets and chart sheets.   The Worksheets collection contains only worksheets.

To retrieve the name of the first sheet in the workbook, use

Public Function FirstSheetName()
    FirstSheetName = Sheets(1).Name
End Function

To retrieve the name of the last sheet in the workbook, use

Public Function LastSheetName()
    LastSheetName = Sheets(Sheets.Count).Name
End Function

You can return an array of all the sheet names with the following

Public Function AllSheetNames()
    Dim Arr() As String
    Dim I as Integer
    Redim Arr(Sheets.Count-1)
    For I = 0 To Sheets.Count - 1
        Arr(i) = Sheets(I+1).Name
    Next I
    AllSheetNames = Arr      ' return a row array OR
    AllSheetNames = Application.Worksheetfunction.Transpose(Arr)
                             ' return a column array
End Function