reference worksheet name in cell in another worksheet
 

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.

reference worksheet name in cell in another worksheet
 

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
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.

reference worksheet name in cell in another worksheet
 

Correction. on the worksheet add line, make it

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


"Margo Guda" wrote:

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.

reference worksheet name in cell in another worksheet
 

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?

Barb Reinhardt wrote:
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
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.

reference worksheet name in cell in another worksheet
 

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.


Barb Reinhardt wrote:
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
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.