PDA

View Full Version : Creating a dynamic list of sheet names



Odyrus
05-10-2011, 06:21 AM
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
05-10-2011, 07:40 AM
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
05-10-2011, 07:57 AM
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
05-10-2011, 08:00 AM
Tá fáilte romhat.

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

Odyrus
05-10-2011, 08:06 AM
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
05-10-2011, 09:48 AM
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
05-10-2011, 11:54 AM
:doh:

Slainte!