Macro to copy and paste link to current sheet

  • Thread starter Thread starter Lindleman
  • Start date Start date
L

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!
 
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!
Click to expand...

Here's an example from a product I've been working on:


Dim LGUP As Worksheet

Dim LEDSRow As Long
Dim LGUPRow As Long

Dim Contents As String

' some names of worksheet tabs
const LEDSDataSheet = "LEDS_Format"
const LargeUpTimesSheet = "Large_Uptimes"

Set LGUP = Sheets(LargeUpTimesSheet)

LEDSRow = 3
LGUPRow = 5

Contents = "=" & LEDSDataSheet & "!" & "$B$" & Mid(Str(LEDSRow),
2)

With LGUP

' prepare a hyperlink directly to this large value
.Hyperlinks.Add Anchor:=LGUP.Cells(LGUPRow, 3), _
Address:="", _
SubAddress:=Contents, _
TextToDisplay:="Go There"
End With


It creates a hyperlink on one sheet to another sheet. This code is
just cut out of a larger subroutine but all the important parts should
be here for a workable example.

I notice that, when it's done, the hyperlink has a full reference to
the destination sheet; it's full name and filesystem location. I
imagine that this will work from one workbook to another.

Brian Herbert Withun
 
Back
Top