hyperlinks

[aesoton]

How can I write a macro that hyperlinks all the cells in a range to sheets that are named after these cells (kind of like an index page).
This is the code that I have generated.

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'2C'!A1", TextToDisplay:="2C"

So I can create a link to a page if I know the name. What I have done is created a process that creates a new page from this specified codes i.e 1C 2C or 3C or whatever. But how I edit the line so that it will automatically take the code and link to the sheet without me having to specify the name in the coding?

Does this make sense

Please Help

Andrew299

 

[jonesy29847]

The following code works for me, I am trying to generate hyperlinks in an Excel Cell automaticly.

Private Sub Workbook_Open()

'GetLength is a function I wrote to determine the first available row in my spreadsheet.

NewRFIRow = GetLength("RFI Log&quot + 1
NewRFIName = NewRFIRow - 1

Length$ = Str$(NewRFIRow)
Length = Len(Length$) - 1
CellToRead = "A" + Right$(Str$(NewRFIRow), Length)

RFIName = "RFI" & NewRFIRow & ".doc"
Hyperlink = "q:\chassis\jones\" & RFIName

Range(CellToRead).Select

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=Hyperlink
End Sub


Hope this helps.

 

[aesoton]

Thanks for getting back to me jonesy
I actually solved this problem quite a while ago the code I am using is

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & xx & "'!A1"

where xx is the name of my sheet. I have removed the text to display bit as this doesnt work in NT and xx is the contents of the cell anyway. The solution to my problem was the inclusion of the & symbols in the code.

Thanks again
Andrew299