スキルアップ・問題解決なら、モーグにおまかせ！

ブックの全てのシート名を取得して、セルまたは変数に格納します。
サンプルマクロの「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