Worksheet names used in cells.

[Mark F.]

How can I insert the worksheet name into a cell of a different worksheet within the same workbook?

 

[Carlos]

OK, here a solution. It assumes the worksheet you want to
know the name is the first in your workbook (i.e, the one
at the left)

go to visual basic and type this into a new module:

Function WKSHN() As String
WKSHN = Sheets(1).Name
End Function

Now type in any cell in the workbook:

=WKSHN() and you'll get the name of the first worksheet in
your workbook.

Hope that helps.

-----Original Message-----
How can I insert the worksheet name into a cell of a

different worksheet within the same workbook?

 

[Mark F.]

I got this to work as you indicate. Thanks. But, it doesn't solve the problem I'm working on

I have a multi-worksheet workbook. I want Excel to dynamically populate the first column of the first sheet with all the other sheet names in the workbook, one row/cell for each sheet number 2 though n.

I found a way to do this by extracting the sheet name from the filename, then referencing that string in the first sheet. But this approach requires the sheet name to also appear in its own respective sheet - and that's what I'm trying to avoid

....any other ideas?...

 

[Gord Dibben]

Mark

Would this help?

''list of sheet names in a workbook - placed on a new worksheet
Sub ShowNames()
Set wkbkToCount = ActiveWorkbook
iRow = 1
With Sheets.Add
'change the preceding line to With Activesheet(no .Add) if you do not want a
new worksheet'
For Each ws In wkbkToCount.Worksheets
.Rows(iRow).Cells(1).Value = ws.Name
iRow = iRow + 1
Next
End With
End Sub

Gord Dibben Excel MVP