すべてのシート名を取得する。

ブック内のすべてシート名を取得するには、For EachステートメントとSheetsコレクションのNameプロパティを利用することで取得できます。
シート名の取得では、Sheetsコレクションに対して繰り返し処理をすることでブック内のシート名を取得させます。
表示されているシート名だけを取得したい場合はVisibleプロパティの情報で判断させる必要があります。

　●すべてのシート名を取得（非表示のシートも含む）

'******************************************************************
'　すべてシート名を取得
'******************************************************************
Sub getSheetName()

    '全シート名を取得する
    Dim oSht As Object
    For Each oSht In Sheets
        'シート名を出力
        Debug.Print oSht.Name
    Next
        
End Sub

　■実行結果

Sheet3は非表示の状態

　
　●表示されているシート名を取得

'******************************************************************
'　表示されているすべてシート名を取得
'******************************************************************
Sub getSheetNameVisibleOnly()

    '全シート名を取得する
    Dim oSht As Object
    For Each oSht In Sheets
        '表示されているシート名だけを出力
        If oSht.Visible Then
            Debug.Print oSht.Name
        End If
    Next
        
End Sub

　■実行結果

Sheet3は非表示の状態