Import Worksheet Name into a Cell

[slmclc77]

What's the easiest way to get the worksheet name into a cell in the worksheet, such that any time the worksheet name is changed, that change is reflected in the cell in the worksheet?

I tried this, but it doesn't work:

Option Explicit
Private Sub Worksheet_Activate()
Dim dayofweek As String
dayofweek = Sheet1.Name
Sheet1.Range("J9") = dayofweek
End Sub

[SammyB]

You don't need code, just use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

However, this will give #VALUE! untill the workbook is saved

[slmclc77]

You don't need code, just use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

However, this will give #VALUE! untill the workbook is saved

Superb! Thanks SammyB!