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

シート名を取得するためには、Nameプロパティを使用します。
サンプルマクロでは、アクティブになっているセルにNameプロパティの値を入力した後に、セルA1にも同じように入力しています。

Sub Sample()

    ActiveCell.Value = ActiveSheet.Name
    MsgBox "アクティブになっているセルにシート名を入力しました"
    
    Range("A1") = ActiveSheet.Name
    MsgBox "セルA1にシート名を入力しました"

End Sub

この処理を、ファイルをオープンした時またはシートがアクティブになった時などに発生するイベントプロシージャに記述すると、イベントが発生すると同時に入力されるようになります。

Private Sub Workbook_Open()
   Range("A1") = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   Range("A1") = ActiveSheet.Name
End Sub