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

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

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.

"Jim Cone" wrote:

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

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
..

"Jim Cone" wrote:
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