Help getting SheetName into a cell

[Occupant]

I need to refer to the sheet name in a formula and can't figure out how to do it.
I can't find a Function which will do this. I did discover ActiveSheet.Name but
I have been unsuccessful in getting it work in a function.

Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c"
and need to get these names into cell C3 of each sheet. The sheets may not be
in order and there may be missing sheets.

Thanks in advance for any help.

Omar

 

[Ken Macksey]

Hi

You could put somthing like this in a macro or attatch to a command button.

For i = 1 To Sheets.Count
Worksheets(i).Range("c3") = Worksheets(i).Name
Next i

HTH

Ken

 

[Frank Kabel]

Hi

you can use the following formula
=MID(CELL("filename";A1),FIND("]";CELL("filename",A1))+1;30)

Frank

 

[Occupant]

Thanks for the response Ken.

I neglected to mention that there are other sheets in the workbook
which I do not want to have the name in cell C3. Also the variable
number of sheets with the "number.alpha" names makes indexing a
for-next loop kind of nasty.

I tried the following but it doesn't work right.
Function GetSheetName() As String
GetSheetName = ActiveSheet.Name
End Function

Is there an object for the current sheet?

Omar

 

[Ron Rosenfeld]

I need to refer to the sheet name in a formula and can't figure out how to do it.
I can't find a Function which will do this. I did discover ActiveSheet.Name but
I have been unsuccessful in getting it work in a function.

Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c"
and need to get these names into cell C3 of each sheet. The sheets may not be
in order and there may be missing sheets.

Thanks in advance for any help.

Omar

How about a VBA macro like:

==============================
Sub WSname()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Cells(3, 3).Value = ws.Name
Next ws

End Sub
===================


--ron

 

[Arvi Laanemets]

Hi

Frank's answer will work, but there are some typos - 2 types of delimiters
("," and ";") are mixed. Depending your Windows settings use
=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;30)
or
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,30)
to get the name of SAME sheet where the formula is placed.
To get the formula working for different sheet, modify it:
=MID(CELL("filename",SheetName!A1),FIND("]",CELL("filename",SheetName!A1))+1
,30)
When the SheetName is chenged (you rename the sheet), the formula is
adjusting automatically

 

[Frank Kabel]

Hi Arvi

thanks for finding my typos . Different language versions in Excel
are really a pain!

Frank

 

[Ron Rosenfeld]

How about a VBA macro like:

==============================
Sub WSname()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Cells(3, 3).Value = ws.Name
Next ws

End Sub
===================


--ron

Reading a subsequent post of yours, it seems there are only certain worksheets
for which you want the name in C3 of that sheet.

Knowing the rules, it should be trivial to add a test for the format into the
above loop.


--ron

 

[Occupant]

Thanks to all those who responded.

I went with Frank's formula after modifying it for Excel97.

I couldn't get Ron's VB Sub to work. It wouldn't accept
ws as a worksheet variable.

I almost got Ken's VB Sub working with the following:
For i = 1 To Sheets.Count
If Worksheets(i).Range("c2").Value = "Foo:Bar" Then
Worksheets(i).Range("c3").Value = "Worksheets(i).Name"
End If
Next i

This put the text string in the appropriate cells, but when I
removed the quotes it hung and I had to use the task manager
to close Excel.

Omar