View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Joe HM Joe HM is offline
external usenet poster
 
Posts: 92
Default Copy Hyperlinks to other cells

Set lHyperLinkList = CreateObject("Scripting.Dictionary")

Hello -

You can use the following to do that. Please note that I store the
hyperlinks in a Scripting.Dictionary for some other reasons. You
might not have to do that. lSheetORG is the original Worksheet and
lSheetOUT is the target Worksheet.

Set lHyperLinkList = CreateObject("Scripting.Dictionary")

For Each lHyperlink In lSheetORG.Hyperlinks
Call lHyperLinkList.Add("C" & lHyperlink.Range.Row,
lHyperlink.Address)
Next lHyperlink

lSheetOUT.Hyperlinks.Delete

lKeys = lHyperLinkList.keys
lItems = lHyperLinkList.items

For i = 0 To lHyperLinkList.Count - 1
Set lRange = lSheetOUT.Range(lKeys(i))

lSheetOUT.Hyperlinks.Add Anchor:=lRange, Address:=lItems(i),
TextToDisplay:=lRange.Value
Next i

Hope this works for you ...
Joe



On Aug 3, 8:28 am, "DaveM" wrote:
Hi all

Been trying to do this for over an hour now, with no joy.

sheet1 A1 to A36, The text values that have hyperlinks to files on harddrive

sheet2 Column A has lots of the same text values as sheet1, some cells have
a little extra text at the end.

how can I get the hyperlinks from sheet1 to all cells that contain the same
text, to sheet2

Thanks in advance

Dave