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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.