Tab naming

[David Aides]

I would like for a workbook sheet tabs (excel 2000) to
automatically re-name themselves from what the sheets "C2"
cell contains.

TFTH,
David

 

[Bob Phillips]

For Each sh In Activeworkbook.Worksheets
sh.Name = sh.Range("C2").Value
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David Aides]

Bob,
Forgive me for not being as good with Excel as I would
like to be, but when I put this code in my workbook VB it
did not work. Please elaborate on how I should do so.

TFTH

 

[David McRitchie]

Hi David,
Directions to install and use a macro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

http://www.mvps.org/dmcritchie/excel/excel.htm

Modifying Bob's code, I didn't test to see what happens
if you try to rename the sheet to the same name, so I just
added a test.

Sub Rename_shts_as_C2()
dim sh as worksheet
For Each sh In Activeworkbook.Worksheets
if sh.Name <> sh.Range("C2").value then
sh.Name = sh.Range("C2").Value
end if
Next sh
End Sub

 

[martialtiger]

Is there anyway to have a cell value display the name of the tab it is
located in?

 

[icestationzbra]

these will display the sheetname (if that is what you meant by tab
name).

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

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

this will display the filename along with the path and sheetname.

=CELL("filename")

this will display only the path of the file.

=MID(CELL("filename"),1,(FIND("[",CELL("filename")))-2)

 

[David McRitchie]

Kind of the opposite to the original question, which I think has been answered.
See my page http://www.mvps.org/dmcritchie/excel/pathname.htm

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

The CELL formulas with "filename" will not work until the file has been saved (#VALUE! error).

 

[martialtiger]

Thanks icestationzbra! That's exactly what I needed! ;

 

[David McRitchie]

three of the four formulas you posted are incorrect because they do not
have a reference cell. Please read the large italic indented text near the
beginning of
Pathname in headings, footers, and cells
http://www.mvps.org/dmcritchie/excel/pathname.htm

Without the reference cell you will pick up the worksheet that was active
dure the last time that calculation took place. That worksheet might
even be in another workbook.