VBA Question

[Phil Osman]

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

 

[Rowan]

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

 

[Robin Hammond]

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]

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]

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]

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!