Using sheet names in worksheet cells

Posted by Mike Day on September 25, 2001 3:34 AM

Is there an easy way to pick up a sheet name and put it in a cell within the worksheet?

e.g. cell A1 '=sheetname', to show the actual name in cell A1

Thanks

Posted by Peter on September 25, 2001 5:27 AM

I think VBA is required. Here's one way. Put this in the workbook module if you want the sheet name in all sheets :-

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

If you only want it for one particlar sheet, put this in the sheet's module :-

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

Posted by Mike on September 25, 2001 5:59 AM

I'll try that, thanks.

Posted by Aladin Akyurek on September 25, 2001 6:19 AM

Mike --

To do it with formulas,

in A1 of a sheet of interest enter: =cell("filename")
in A2 enter: =RIGHT(A1,LEN(A1)-SEARCH("]",A1))

Aladin

Posted by Mike on September 25, 2001 6:57 AM

That's just the job - thanks