Get All Worksheets Name by Array Function :

If You want to Get all Existing worksheet in a Workbook you can use this Array Function :
Suppose you have 100 Worksheet in a Workbook and You want all sheet name in a sheet then select any 100 cells Type this Function name and press Shift+Ctrl+Enter , You will get all sheet Names :


Function Sheetname() As Variant
Application.Volatile
Dim Sheet() As Variant
Dim r As Integer
Dim c As Integer
Dim i As Integer

With Application.Caller
        r = .Rows.Count
        c = .Columns.Count
End With

ReDim Sheet(1 To r, 1 To c)
    i = 1
    For j = 1 To r
        For k = 1 To c
        If i > Sheets.Count Then Exit For
        Sheet(j, k) = Sheets(i).Name
        i = i + 1
    Next
Next

For j = 1 To r
    For k = 1 To c
        If Sheet(j, k) = 0 Then Sheet(j, k) = ""
    Next
Next
Sheetname = Sheet
End Function