current sheetname into cell

[spilner]

hi,ive this code from somewhere in this forum.

Function WhoAmI() As String

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

Application.Volatile
WhoAmI = ws.Name

Set wb = Nothing
Set ws = Nothing

End Function


use =WhoAmI()

this code is working great but i want the number in 6 digits.

example if the sheetname is 5.then in the cell i want it as 000005.
if 700 then 000700

can someone help?thanks a lot

 

[VoG]

Try

WhoAmI = Format(ws.Name, "000000")

 

[spilner]

its not working.
one thing is if i create a copy of the sheet name.the cell equal to the other cell.not the current cell.

example current sheetname is 60
i create a copy of 60 sheetname.in the cell show not show 60 (2).still show 60 every of the new copy sheets.

thanks for the help tho

 

[anandvarma]

try this formula

=TEXT(SHEETNAME(),"000000")

 

[spilner]

i dont know why it gave me #NAME?

 

[anandvarma]

i am using excel 2007


A1: 000700

Spreadsheet Formulas
Cell Formula
A1 =TEXT(SHEETNAME(),"000000")

 

[VoG]

Make sure the file is saved first then

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

 

[spilner]

yours working perfect VOG.thank you so much man