Excel Hyperlink Transfer

[Bob500]

Hi,

I am using the following code to copy across rows of data from one excel sheet with rows of information to another blank sheet. It works but some of the data stored on the main sheet are hyperlinks, and when I copy them across only the text is transferred, hyperlink is lost.

Any ideas on how to transfer the actual link across as well?

Private Sub CommandButton1_Click()
Dim iListCount As Integer, iColCount As Integer
Dim iRow As Integer
Dim rStartCell As Range

'Set a range variable to the first cell to recieve data
'Using "End(xlUp).Offset(1, 0)" _
 will give us the cell below the last entry
Set rStartCell = Sheet2.Range("A65536").End(xlUp).Offset(1, 0)

  'Loop as many times (less one) as there are entries in our list.
  'We must start from zero to use this in the Selected Property.
  For iListCount = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(iListCount) = True Then  'User has selected
      ListBox1.Selected(iListCount) = False
      iRow = iRow + 1
      'Now loop as many times as there are columns in MyRange
      For iColCount = 0 To Range("MyRange").Columns.Count - 1
      'place the selected data into the table, starting from _
       range Ax and moving across as many columns as there are _
       in the range MyRange.
       rStartCell.Cells(iRow, iColCount + 1).Value = _
       ListBox1.List(iListCount, iColCount)
      Next iColCount
   End If
Next iListCount

Set rStartCell = Nothing

End Sub

Many thanks

 

[Bob500]

I have figured a way of doing it, by putting an extra column with the code =hyperlink(name of cell) down it and then hiding the column that is transferred, give the 'illusion' that the link is copied.

But is there a better way to do this?

Thanks