Creating the name of a worksheet in a cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
One way. Place in the ThisWorkbook module.

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

Try this one.

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

Regards,
Kari J Keinonen
 
Back
Top