copy cell value as an address to create a hyper link

Hey everyone.
need help here.


i have a sheet with two columns filled with hyperlinks.
i would like to copy them to a new sheet but with a display as text.

example
in sheet 1:
A B C
Google www.google.com maps.google.com
Yahoo www.yahoo.com maps.yahoo.com

in sheet two, i need:
A B C
Google Main Page Maps
Yahoo Main Page Maps

note that the word Main page in B1 should have a hyper link to the link in B1 of sheet 1
and the word maps in C1 should have a hyper link to the link in C1 of sheet 1
and the word Main page in B2 should have a hyper link to the link in B2
and the word maps in C2 should have a hyper link to the link in C2

i tried the record button in excel (newbie choice), with text, it works perfect. this is the function i got:

Sub TestName()
'
' TestName Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    ActiveCell.Select
    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
End Sub

it works fine, but if i record, i cannot paste a hyperlink from sheet 1 into the "create hyperlink" window, unless i copy the hyperlink by F2 the cell and copy. if i do this, macro will record the actual hyperlink. so if i do this to the next cell, i will end up with the same hyper link.

note that i prefer that there is no range, i mean. one cell at a time. i understand i have to make sure both active cells are the correct ones. but it's ok. i will use ActiveCell.Offset(0, 1).Range("A1").Select with different numbers as needed .at least i would have control over what is happening.

the second reason for the one row at a time is that i will be adding entries to the first sheet. so what i would like is. i highlight the new entry. go to the next blank cell in sheet 2, run the macro to get the results i like.

so, i guess it's a simple, how do i copy a "cell value" then paste it as an address in this funtion:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        value of Sheet1'B1  _
        , TextToDisplay:="Main page"

Thank you for your help

I had trouble wit hthe edit botton, so i will add this here,

this is the newbie code i wanna use

Sub NewTest()
'
' NewTest Macro
'
' Keyboard Shortcut: Ctrl+Shift+W
'
    Sheets("Sheet1").Select
    ActiveCell.Offset(1, -2).Range("A1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "the link selected in Sheet 1"
    Sheets("Sheet2").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "the link copied from Sheet 1" _
        , TextToDisplay:="Main Page"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "The link selected in Sheet 1"
    Sheets("Sheet2").Select
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "the link copied from Sheet 1" _
        , TextToDisplay:="Maps"
    Sheets("Sheet1").Select
    ActiveCell.Offset(1, -2).Range("A1").Select
End Sub

how bad is this, lol