シート名を取得するためには、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