Excel Macro Problem

[Ian Lowe]

From within an Excel macro I am trying to automatically generate
series of HTML links on the 1st Worksheet of an Excel Workbook whic
point to a variable numer of subsequent worksheets. (See cod
attached)

The problem I have is that I can easly see how to create links t
external locations but can't find the right reference format to poin
to internal Worksheet locations. Can any one help?

For i = 2 To Sheets.Count
With Worksheets("Main")
Cells(i + 6, 4).Value = Sheets(i).Name
.Hyperlinks.Add Anchor:=.Cells(i + 6, 4), Address:=Worksheets(i).Name
End With
For rwIndex = 1 To 1000000
With Worksheets(i).Cells(rwIndex, 1)
If .Value = "Total" Then
Worksheets("Main").Cells(i + 6, 6).Value = Worksheets(i).Cells(rwIndex
6)
Worksheets("Main").Cells(i + 6, 8).Value = Worksheets(i).Cells(rwIndex
8)
rwIndex = 1000000
End If
End With
Next rwIndex
Next

 

[Dave Peterson]

You may want to see how David McRitchie did it in his "build table of contents"
code:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

but this worked ok for me:

.Hyperlinks.Add Anchor:=.Cells(i + 6, 4), Address:="", _
SubAddress:="'" & Worksheets(i).Name & "'!c99"

I recorded a macro when I did it manually and got the code.

 

[Ian Lowe]

Dave, many thanks I used your code and it worked fine.

I will take a look at David's code but for now I am happy