Creating the name of a worksheet in a cell

[Guest]

Hi...Happy Christmas

I've written a custom function to provide the worksheet name in a cell...

Function sht_name()
sht_name = ActiveSheet.Name
End Function

I've copied this to other sheets, but it doesn't appear to update for the
new sheet name even when I press F9

Really I want the sheet name to be there irrespective of whether the sheet
is the active one or not...it'll save my typing them individually for 60
sheets....

Your help is appreciated as always....Chris

 

[Don Guillett]

One way. Place in the ThisWorkbook module.

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

 

[Guest]

Hi!

Try this one.

Function sht_name()
Application.Volatile True
sht_name = Application.Caller.Parent.Name
End Function

Regards,
Kari J Keinonen