Hyperlink Alternate Sheets

D

Dolphinv4

Hi,

I have a macro that list all the sheets i have and this listing will
hyperlink to all the sheets automatically. What i want instead is to have the
macro list alternate sheets instead, for ex, i have the following sheets:

Main (Listing of all sheets)
AAA
AAA-A
BBB
BBB-A
CCC
CCC-A

I want the macro to only, in the "Main" Sheet, to only list the following
sheets:

Main
AAA
BBB
CCC

How can I do that with the macro? Below is my current code.

Sub WorkSheetNames()

Range("A:A").Select
Selection.ClearContents


Dim a As Long
Dim s As Long
Dim shtName As String
s = 1
For a = 1 To Sheets.Count - 3
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a


Range("A1").Select
Selection.Font.Bold = True

End Sub

Thanks.

Dolphinv4
 
J

Jim Cone

Sub WorkSheetNames()
Dim a As Long
Dim s As Long
Dim shtName As String
s = 1
Range("A:A").ClearContents

For a = 2 To (Sheets.Count - 3) Step 2
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a + 1, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a

Range("A1").Font.Bold = True
Range("A1").Value = "Main"
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dolphinv4"
wrote in message
Hi,
I have a macro that list all the sheets i have and this listing will
hyperlink to all the sheets automatically. What i want instead is to have the
macro list alternate sheets instead, for ex, i have the following sheets:

Main (Listing of all sheets)
AAA
AAA-A
BBB
BBB-A
CCC
CCC-A

I want the macro to only, in the "Main" Sheet, to only list the following
sheets:

Main
AAA
BBB
CCC

How can I do that with the macro? Below is my current code.

Sub WorkSheetNames()
Range("A:A").Select
Selection.ClearContents
Dim a As Long
Dim s As Long
Dim shtName As String
s = 1
For a = 1 To Sheets.Count - 3
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(a, s), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
Next a
Range("A1").Select
Selection.Font.Bold = True
End Sub

Thanks.
Dolphinv4
 
D

Dolphinv4

Hi,

but then there'll be empty rows in between. Is there anyway these empty rows
won't be reflected or can be deleted?

Thanks.
 
J

Jim Cone

Anything else?
'--
Sub WorkSheetNames()
Dim a As Long
Dim R As Long
Dim shtName As String
R = 2
Range("A:A").ClearContents
For a = 2 To (Sheets.Count - 3) Step 2
shtName = Sheets(a).Name
Sheets(a).Hyperlinks.Add anchor:=Cells(R, 1), Address:="", SubAddress:="'" _
& shtName & "'!A1", ScreenTip:=shtName, TextToDisplay:=shtName
R = R + 1
Next a
Range("A1").Font.Bold = True
Range("A1").Value = "Main"
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Dolphinv4"
wrote in message
Hi,
but then there'll be empty rows in between. Is there anyway these empty rows
won't be reflected or can be deleted?
Thanks
..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top