Need hyperlink to move to another worksheet with rest of the row

[DJ_Rutts]

Hi guys,

I am wondering if someone could help with this..

I have a VBA which moves a row from the outstanding work sheet to a completed work sheet when the status in column J is changed to 'Complete'. In column A is the Job name which I have set as a hyperlink to a individual folder for that project.

My problem is that when the job is complete and it moves to another worksheet, it does not carry the hyperlink with it.

Anybody know of a way to do this??

I have attached the file for reference.

Thanks

Dan

[jaslake]

Hi DJ_Rutts

I've used this code with mixed success...you may wish to play with it

Function Completed(oRow As Long)            'This is data that transfers from outstanding to completed spreadsheet
    Dim wsO As Worksheet, wsC As Worksheet, cRow As Long
    Dim HyperAddr As String
    Dim rngDst As Range
    Set wsO = Sheets("Outstanding")
    Set wsC = Sheets("Complete")
    HyperAddr = GetAddress(wsO.Cells(oRow, "A"))
    cRow = wsC.Range("A" & Rows.Count).End(xlUp).Row + 1
    Set rngDst = wsC.Cells(cRow, "A")
    wsC.Cells(cRow, "A").Value = wsO.Cells(oRow, "A").Value
    wsC.Cells(cRow, "B").Value = wsO.Cells(oRow, "B").Value
    wsC.Cells(cRow, "C").Value = wsO.Cells(oRow, "C").Value
    wsC.Cells(cRow, "D").Value = wsO.Cells(oRow, "D").Value
    wsC.Cells(cRow, "E").Value = wsO.Cells(oRow, "E").Value
    wsC.Cells(cRow, "F").Value = wsO.Cells(oRow, "G").Value
    wsC.Cells(cRow, "H").Value = wsO.Cells(oRow, "K").Value
    wsC.Cells(cRow, "L").Value = wsO.Cells(oRow, "L").Value
    wsC.Cells(cRow, "M").Value = Date
    wsC.Hyperlinks.Add rngDst, HyperAddr
    wsO.Range("A" & oRow & ":O" & oRow).ClearContents
    wsO.Range("A" & oRow & ":O" & oRow).Interior.Color = xlNone
End Function
 
 
Function GetAddress(HyperlinkCell As Range)
    GetAddress = Replace _
    (HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function