Worksheet name in a cell?

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

...but, you must Save your workbook before this
formula will work.

Sub WbookNameInCell()
Workbooks("Book2").Worksheets(1).Range("A1").Value = ThisWorkbook.Name
End Sub

Mark....

Agian you amaze me, but why the A1, I tried it with just:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

and it's fine for me

Comments

Ian Mac

Interesting...

The help topic for CELL worksheet function shows
the CELL worksheet function's 2nd argument in
bold characters which normally indicates that the
argument NOT optional. Evidently, the help topic
is incorrect. : =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) : ...but, you must Save your workbook before this

Even more Interesting...and an answer!!

I've just placed the same formula in three different sheets AND different cells, the result is they ALL say the same thing (the last sheet where the cell with the formula was activated/entered). BUT when I used your formula it obviously referenced a cell in the same sheet and hey presto! the sheet name.

Ian Mac

Cell ref ensures anchoring. [NT]

Another way .....

Define two names :-.

Name : sh
Refers to : GET.DOCUMENT(76)

Name : wb
Refers to : GET.DOCUMENT(88)

To return the sheet name, enter in any cell :-
=RIGHT(sh,LEN(sh)-LEN(wb)-2)

Also, you can get the Workbook name from =wb
And, the [Workbookname]Sheetname from =sh