reference worksheet name in cell in another worksheet

[Margo Guda]

Hi,
I was looking for information on how to reference the name of a
worksheet in a cell in another worksheet, and I found a number of
references in the newsgroup, however they have all expired. The MS KB is
singularly unhelpful in this respect. Can anyone help me find the info
I'm looking for?
Specifically, I need to make a list of the names of a number of
worksheets, in another worksheet in the same workbook, and refer to
cells on these worksheets using these found names. I have seen this
described on this newsgroup but cannot access the reference.

Thanks in advance.

 

[Guest]

Well, to get a list of the names of the worksheets, you need something like
this:

Sub ListWOrksheets()

Dim myWS As Worksheet
Dim newSht As Worksheet

Set newSht = Nothing
On Error Resume Next
Set newSht = Worksheets("Worksheet List")
On Error GoTo 0
If newSht Is Nothing Then
Set newSht = Worksheets.Add(After:=ActiveWorkbook.Worksheets(1))
newSht.Name = "Worksheet List"
End If

newSht.Cells(1, 1) = "Worksheet Name"
lrow = 1
For Each myWS In ActiveWorkbook.Worksheets
If myWS.Name <> newSht.Name Then
lrow = lrow + 1
newSht.Cells(lrow, 1).Value = myWS.Name
End If
Next myWS

End Sub

HTH,
Barb Reinhardt

 

[Guest]

Correction. on the worksheet add line, make it

Set newSht =
Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))

 

[Margo Guda]

Thanks Barb,
Fantastic! Yes it helps some. However I was thinking more of something
like a function so that the result of the calculation can be copied,
referenced, etc. Can this subroutine be rewritten as a function? How
would I go about that? I guess it would have to be an array function,
and you would not beforehand necessarily know how many elements would go
in the array?

 

[Margo Guda]

Barb,

I have found what I needed (on one of those hidden sites on the web)!
(actually not so hidden, but old... but it still works, and does what I
want).
Thanks all the same for your help and effort, it is much appreciated.