Creating a dynamic list of sheet names

[Odyrus]

Hello all,

I have some simple code that will list all the sheets in my workbook:

Sub GetWSNames()
For i = 1 To Sheets.Count
Range(”A” & i) = Sheets(i).Name
Next i
End Sub


I'd like to improve upon this by adding hyperlinks to the sheets the code lists.

Anyone have any suggestion, or can point me in the proper direction?

Cheers!

[Bob Phillips]

Sub GetWSNames()
Dim ws As String
Dim i As Long

With ActiveSheet

For i = 1 To .Parent.Worksheets.Count

ws = .Parent.Worksheets(i).Name
.Range("A" & i).Value = ws
.Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:="'" & ws & "'!A1"
Next i
End With
End Sub

[Odyrus]

Thanks for the reply xld, that did the trick!

Now, when I try to include an offset so it goes to the next available row I'm getting an error. Any thoughts?


Sub GetWSNames()
Dim ws As String
Dim i As Long

With ActiveSheet

For i = 1 To .Parent.Worksheets.Count

ws = .Parent.Worksheets(i).Name
.Range("A" & i & Rows.Count).End(xlUp).Offset(1).Select.Value = ws
.Hyperlinks.Add Anchor:=.Range("A" & i), Address:="", SubAddress:="'" & ws & "'!A1"
Next i
End With
End Sub
Go raibh maith agat :thumb

[Bob Phillips]

Tá fáilte romhat.

Why are you trying that, the loop index already moves it down?

[Odyrus]

I have header information in the first few rows of my spreadsheet. When I ran the code you provided it copied over that information.

[Bob Phillips]

I see. Just offset it by the number of header rows



Sub GetWSNames()
Dim ws As String
Dim i As Long

With ActiveSheet

For i = 1 To .Parent.Worksheets.Count

ws = .Parent.Worksheets(i).Name
.Range("A" & i + x).Value = ws
.Hyperlinks.Add Anchor:=.Range("A" & i + x), Address:="", SubAddress:="'" & ws & "'!A1"
Next i
End With
End Sub

replace the + x with that number

[Odyrus]

:doh:

Slainte!