ブックの全てのシート名を取得して、セルまたは変数に格納します。
　サンプルマクロの「Sample1」と「Sample2」は、セルに格納しています。
　サンプルマクロの「Sample3」は、変数に格納してイミディエイトウィンドウに
変数に格納した値を出力しています。

Sub Sample1()

    Dim objWorkSheet As Worksheet
    Dim intRow       As Integer

    intRow = 1
    For Each objWorkSheet In Worksheets
        Sheets("Sheet1").Cells(intRow, 1).Value = _
                                    objWorkSheet.Name
        intRow = intRow + 1
    Next
    
End Sub

Sub Sample2()

    Dim i          As Integer
    Dim mySheetCnt As Integer
    Dim mySheetNam As String

    mySheetCnt = ThisWorkbook.Sheets.Count

    For i = 1 To mySheetCnt
        mySheetNam = Sheets(i).Name
        Sheets("Sheet1").Cells(i, 1) = mySheetNam
    Next i

End Sub

Sub Sample3()

    Dim i          As Integer
    Dim mySheetCnt As Integer
    Dim mySheetNam() As String

    mySheetCnt = ThisWorkbook.Sheets.Count

    ReDim mySheetNam(1 To mySheetCnt) As String

    For i = 1 To mySheetCnt
        mySheetNam(i) = Sheets(i).Name
        Debug.Print "変数mySheetNam(" & i & ")=" & mySheetNam(i)
    Next i

End Sub

【イミディエイトウィンドウに出力される例】
変数mySheetNam(1)=Sheet1
変数mySheetNam(2)=Sheet2
変数mySheetNam(3)=Sheet3
変数mySheetNam(4)=Sheet4