In Sheet1, cell A1 has the formula=Sheet2!B3. Cell A1 (Sheet1) also
contains a hyperlink that is linked to Sheet2!B3

Now what I want to do is to copy the formula & the hyperlink in
sheet1A1 down to cell A2 and all the way to cell A250.

The issue is when I do the copy, the formula is copied but the
hyperlink still points to Sheet2!B3.

Any thoughts or ideas?

Thanks a lot.

Jijy


--
jijy
------------------------------------------------------------------------
jijy's Profile: http://www.excelforum.com/member.php...o&userid=15042
View this thread: http://www.excelforum.com/showthread...hreadid=553894

Insert this code into a code module, then select all the cells. While
they're still selected, use Tools | Macro | Macros to choose and run the code.

Sub CreateInternalDocumentHyperlink()

Dim Cell As Object

For Each Cell In Selection

ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:="", _
SubAddress:=Right$(Cell.Formula, Len(Cell.Formula) - 1)

Next

End Sub

"jijy" wrote:


In Sheet1, cell A1 has the formula=Sheet2!B3. Cell A1 (Sheet1) also
contains a hyperlink that is linked to Sheet2!B3

Now what I want to do is to copy the formula & the hyperlink in
sheet1A1 down to cell A2 and all the way to cell A250.

The issue is when I do the copy, the formula is copied but the
hyperlink still points to Sheet2!B3.

Any thoughts or ideas?

Thanks a lot.

Jijy


--
jijy
------------------------------------------------------------------------
jijy's Profile: http://www.excelforum.com/member.php...o&userid=15042
View this thread: http://www.excelforum.com/showthread...hreadid=553894

The code creates the link but when I click on the link it doesn't take
me to the destination cell. Irrespective of which cell I click the
link, it will take me to one particular cell in Sheet2.
Is it possible to click on a cell and then the link takes you to the
exact cell where the item appears in Sheet2?

Thanks a lot for helping out.


--
jijy
------------------------------------------------------------------------
jijy's Profile: http://www.excelforum.com/member.php...o&userid=15042
View this thread: http://www.excelforum.com/showthread...hreadid=553894

It should take you there. Let's say that the formula in a cell is
=Sheet2!B99 then the link will be set up as Address:="", SubAddress:=
"Sheet2!B99"

I just double checked this in Excel 2003 here and it works properly and
takes me to the correct cell on the other worksheet. Perhaps check the
formulas in the cells that you're working with and make sure that somehow
they didn't all end up pointing to just one place in the workbook?

"jijy" wrote:


The code creates the link but when I click on the link it doesn't take
me to the destination cell. Irrespective of which cell I click the
link, it will take me to one particular cell in Sheet2.
Is it possible to click on a cell and then the link takes you to the
exact cell where the item appears in Sheet2?

Thanks a lot for helping out.


--
jijy
------------------------------------------------------------------------
jijy's Profile: http://www.excelforum.com/member.php...o&userid=15042
View this thread: http://www.excelforum.com/showthread...hreadid=553894