Hyperlink in a vba

[gregols]

Hi I'm running a workbook with numerous worksheets. The first worksheet is a summary page where I use the following macro to collect certain info from each worksheet:
Sub MakeSummary()
'
' MakeSummary Macro

'
Sheets("SUMMARY").Select
' Clear the existing values (if any)
Range("$bb$2:$be$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
J = 2
For I = 3 To Sheets.Count
A$ = Sheets(I).Name
' Don't process a sheet if its name is "Conversion Table"
' or if the name is blank.
If (A$ = "Conversion Table") Then GoTo 10
If (Sheets(A$).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
Range("bB" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R6c3"
Range("bC" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R71c1"
Range("bD" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R71C2"
Range("bE" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R71C3"
J = J + 1
10 Next I
End Sub

Is there a code that can be added to this macro that would give me a hyperlinked list of worksheet tabs in column BA

 

[PHV]

A starting point

With Sheets("SUMMARY")
  .Hyperlinks.Add Anchor:=.Range("BA" & J), Address:="", SubAddress:=A$ & "!A1", TextToDisplay:=A$
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gregols]

This works but only on the last sheet on the list does the hyperlink work. Any ideas why it's only picks this one?

 

[HarleyQuinn]

How have you integrated the example into your code?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[gregols]

Tried it my code as:

Sub MakeSummary()
'
' MakeSummary Macro
Sheets("SUMMARY").Select
' Clear the existing values (if any)
Range("$bb$2:$be$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
j = 2
For I = 3 To Sheets.Count
A$ = Sheets(I).Name
' Don't process a sheet if its name is "Conversion Table"
' or if the name is blank.
If (A$ = "Conversion Table") Then GoTo 10
If (Sheets(A$).Range("$C$1").Value = "") Then GoTo 10
With Sheets("Summary")
.Hyperlinks.Add Anchor:=Range("ba" & j), Address:="", SubAddress:=A$ & "A1", TextToDisplay:=A$
End With
' Process the current sheet
Range("bB" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R6c3"
Range("bC" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R71c1"
Range("bD" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R71C2"
Range("bE" + Format(j)).FormulaR1C1 = "='" + A$ + "'!R71C3"
j = j + 1
10 Next I
End Sub

And the list of sheets appear and are hyperlinked but if you click on them it comes up with reference is not valid.

 

[PHV]

.Hyperlinks.Add Anchor:=.Range("ba" & j), Address:="", SubAddress:=A$ & "[!]![/!]A1", TextToDisplay:=A$

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886