Hyperlinks from one excel sheet to another in VBA

[Lindleman]

I am trying to programmatically insert a hyperlink into a cell on
sheetA (static name) from various other workbooks whose names all
differ. I want to be able to open the referenced sheets from a master
sheet. I tried to record a macro that copies and pastes as a link, but
it does not record the paste link function. Since the sheet names are
all variables except for the master, I know somewhere I need a cell
reference. i.e. Cell("filename"), but I need this to run inside a VBA
macro. Can anyone help me on this code?

Thanks in advance!

 

[Guest]

Try something like this:

Sub hyper_put()
Dim s As String, t As String
s = Chr(34)
t = "=HYPERLINK(" & s
t = t & "file:///C:\Documents and Settings\Owner\Desktop\Book2.xls#Sheet2!B9"
t = t & s & ")"
Range("A1").Value = t
End Sub

The contents of A1 will be:

=HYPERLINK("file:///C:\Documents and
Settings\Owner\Desktop\Book2.xls#Sheet2!B9")

 

[Lindleman]

I am trying to programmatically insert a hyperlink into a cell on
sheetA (static name) from various other workbooks whose names all
differ. I want to be able to open the referenced sheets from a master
sheet. I tried to record a macro that copies and pastes as a link, but
it does not record the paste link function. Since the sheet names are
all variables except for the master, I know somewhere I need a cell
reference. i.e. Cell("filename"), but I need this to run inside a VBA
macro. Can anyone help me on this code?

Thanks in advance!

Can anyone help me?