Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VBA Question
Sub sheets_list()
Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing to a = 5 makes the macro only start returning the names from Sheet 5 in the workbook onwards. I want to return the entire list of worksheets in Cell D5. Can someone help please as I'm sure there is just something easy I need to change.....thanks! Phil |
#2
|
|||
|
|||
Try this: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a+4, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub "Phil Osman" wrote: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing to a = 5 makes the macro only start returning the names from Sheet 5 in the workbook onwards. I want to return the entire list of worksheets in Cell D5. Can someone help please as I'm sure there is just something easy I need to change.....thanks! Phil |
#3
|
|||
|
|||
Phil,
Just add another variable to hold the row. Sub sheets_list() Dim a As Long Dim s As Long Dim r as Long Dim shtName As String s = 4 r=5 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(r+a-1, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub Robin Hammond www.enhanceddatasystems.com "Phil Osman" wrote in message ... Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing to a = 5 makes the macro only start returning the names from Sheet 5 in the workbook onwards. I want to return the entire list of worksheets in Cell D5. Can someone help please as I'm sure there is just something easy I need to change.....thanks! Phil |
#4
|
|||
|
|||
This works great!
Is there anyway to now get the list of worksheet names to be returned on more than one sheet ? eg. I have a contents sheet that I want to show the list when I run the macro, but also I have a lookup sheet that I want to show the updated list. "Rowan" wrote: Try this: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a+4, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub "Phil Osman" wrote: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing to a = 5 makes the macro only start returning the names from Sheet 5 in the workbook onwards. I want to return the entire list of worksheets in Cell D5. Can someone help please as I'm sure there is just something easy I need to change.....thanks! Phil |
#5
|
|||
|
|||
The macro is setup to populate the activesheet. So if you select the contents
sheet and run it you will get the list there. If you then select the lookup sheet and run the macro again you will get the list of hyperlinks on the lookup sheet as well. Rowan "Phil Osman" wrote: This works great! Is there anyway to now get the list of worksheet names to be returned on more than one sheet ? eg. I have a contents sheet that I want to show the list when I run the macro, but also I have a lookup sheet that I want to show the updated list. "Rowan" wrote: Try this: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a+4, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub "Phil Osman" wrote: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing to a = 5 makes the macro only start returning the names from Sheet 5 in the workbook onwards. I want to return the entire list of worksheets in Cell D5. Can someone help please as I'm sure there is just something easy I need to change.....thanks! Phil |
#6
|
|||
|
|||
I tried copy to the code within the macro and added a
Sheets("contents").select and Sheets("assumptions").select before each lot of the code but it didn't work. I think I will just embed the macro you gave me within another one that just selects a sheet, runs the list sheets macro, selects another sheet, runs it again etc. That should work. Thanks! "Rowan" wrote: The macro is setup to populate the activesheet. So if you select the contents sheet and run it you will get the list there. If you then select the lookup sheet and run the macro again you will get the list of hyperlinks on the lookup sheet as well. Rowan "Phil Osman" wrote: This works great! Is there anyway to now get the list of worksheet names to be returned on more than one sheet ? eg. I have a contents sheet that I want to show the list when I run the macro, but also I have a lookup sheet that I want to show the updated list. "Rowan" wrote: Try this: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a+4, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub "Phil Osman" wrote: Sub sheets_list() Dim a As Long Dim s As Long Dim shtName As String s = 4 For a = 1 To Sheets.Count shtName = Sheets(a).Name Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _ & shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName Next a End Sub My understanding is that the s = 4 determines the column where the list of worksheets should be returned, and this seems to be correct when I change the value. Also if I change a = 1 to a = 5 it starts the list in Cell D5 which is what I want. BUT......changing to a = 5 makes the macro only start returning the names from Sheet 5 in the workbook onwards. I want to return the entire list of worksheets in Cell D5. Can someone help please as I'm sure there is just something easy I need to change.....thanks! Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel 2002 Master Template Question | Excel Discussion (Misc queries) | |||
VBA Form ComboBox question | Excel Worksheet Functions | |||
Question about combining data from multiple workbooks into one rep | Excel Discussion (Misc queries) | |||
An easy macro question and one I believe to be a little more diffi | Excel Worksheet Functions |