List Sheet & Hyperlink
 

Hi,

i have this macro which used to work but now it doesn't. This macro will
list all the sheets i have excluding those which ends with "-A". Now when i
click on the hyperlink, it shows a dialogue box "That name is not valid". Why
is this so?

Thanks.

Sub List_Hyperlink()

Range("A:A").ClearContents

x = 1
For a = 1 To (Sheets.Count - 14)
shtName = Sheets(a).Name
If Right(shtName, 2) < "-A" Then
Sheets(a).Hyperlinks.Add anchor:=Cells(x, 1), Address:="",
SubAddress:="'" & shtName
x = x + 1
End If
Next a

End Sub

List Sheet & Hyperlink
 

Try:

SubAddress:=shtName & "!A1"

--
Gary''s Student - gsnu200762


"Dolphinv4" wrote:

Hi,

i have this macro which used to work but now it doesn't. This macro will
list all the sheets i have excluding those which ends with "-A". Now when i
click on the hyperlink, it shows a dialogue box "That name is not valid". Why
is this so?

Thanks.

Sub List_Hyperlink()

Range("A:A").ClearContents

x = 1
For a = 1 To (Sheets.Count - 14)
shtName = Sheets(a).Name
If Right(shtName, 2) < "-A" Then
Sheets(a).Hyperlinks.Add anchor:=Cells(x, 1), Address:="",
SubAddress:="'" & shtName
x = x + 1
End If
Next a

End Sub