Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
How to reference cell in other worksheet | Excel Worksheet Functions | |||
I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions |