How do you get a sheet name to print in a Cell?

This question often comes up when I am showing how to link between sheets.

From: http://www.ozgrid.com/VBA/return-sheet-name.htm

“Return an Excel Worksheet Name to a Cell

In Excel it is possible to use the CELL function/formula and the MID and FIND to return the name of an Excel Worksheet in a Workbook. The formula below shows us how:

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,256)

Where A1 is any non error cell on the Worksheet. If you want the full path of the Excel Workbook, simply use;

=CELL(“filename”,A1)

It is important to note that the above formulas will only work in a Workbook that has been saved”

Referred web site has a vba example as well.

Steven