I've checked out Chandoo's extensive summary on Hyperlinks but still getting myself in a pickle... !!
Found this out there out there on the net... courtesy of someone called Webtest
[pre]
[/pre]
I like this because it retains the format in the destination cell.
Tried adapting this to suit my needs... but failing miserably... what I would like, is to be able to pass a cell range (single cell or more) and destination range into the routine independent of being on the same worksheet - the above uses the offset and hence only works when the source and destination are on the same worksheet ie. copy the contents of wsSrc!B3 to wsDst!E7 irrespective of whether wsSRc and wsDst are the same worksheets or not.
eg. CopyHyperlink("AWorksheet!B3", "AnotherWorksheet!E7") whilst retaining the format of the destination cell
Any assistance gratefully received
Thanks
Found this out there out there on the net... courtesy of someone called Webtest
[pre]
Code:
Sub HyperlinkMoveTest()
Dim aSheet As Worksheet 'Sheet Handle
Dim hLink As Hyperlink 'Hyperlink Handle
Dim rngDst As Range 'Destination Cell
Dim rngSrc As Range 'Source Cell
'Set a Handle for the working Sheet
Set aSheet = ActiveWorkbook.Sheets("TEST")
'Iterate through all the Hyperlinks in a range
For Each hLink In aSheet.Range("B1:B18").Hyperlinks
'Fetch the cell address of the source Hyperlink
Set rngSrc = hLink.Range
'Set the Destination Cell address based on the Source Cell address
Set rngDst = rngSrc.Offset(0, 2)
'Load the Destination Cell with the Hyperlink WITHOUT AFFECTING THE FORMAT !!!
aSheet.Hyperlinks.Add rngDst, hLink.Address, , , hLink.Range.Text
Next hLink
End Sub
I like this because it retains the format in the destination cell.
Tried adapting this to suit my needs... but failing miserably... what I would like, is to be able to pass a cell range (single cell or more) and destination range into the routine independent of being on the same worksheet - the above uses the offset and hence only works when the source and destination are on the same worksheet ie. copy the contents of wsSrc!B3 to wsDst!E7 irrespective of whether wsSRc and wsDst are the same worksheets or not.
eg. CopyHyperlink("AWorksheet!B3", "AnotherWorksheet!E7") whilst retaining the format of the destination cell
Any assistance gratefully received
Thanks