VBA Hyperlink using variable

[gmain1978]

Hi,
I need some help if possible. I have a workbook with some information in a table ("Sheet!") which gets added to information in a form in a second sheet ("Sheet3"), sheet3 then gets saved using a variable from cell I6 as an excel sheet. If possible I would like to add a hyperlink into the first sheet row (column AD) showing that the information was sent and the link to the saved excel sheet. Hopefully this makes sense.

 

[gmain1978]

This is the first part of the code to save the file, I then have another sub (SendNCR) to generate an email and attach this saved sheet.

ub SaveNCR()
'
'
Dim Fname   As String
Dim FPath   As String
Dim NCR_No As String
NCR_No = Range("I6")
Fname = "NCR " & NCR_No & ".xlsx"
FPath = "\\Network_share\users\NEW QUALITY NCR SYSTEM\RETURNED NCR'S\"
'
    Range("A1:J66").Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    'Selection.PasteSpecial Paste:=xlPasteImage, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ChDir "\\Network_share\users\NEW QUALITY NCR SYSTEM\RETURNED NCR'S"
    ActiveWorkbook.SaveAs Filename:= _
        FPath & Fname, FileFormat _
        :=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    ActiveWindow.SmallScroll Down:=-76
    Call SendNCR
 
    
End Sub

 

[gmain1978]

I managed to get it done with the following.

 Dim ra As Range

    Set ra = Cells.Find(What:=NCR_No, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    If ra Is Nothing Then
        MsgBox ("Not found")
        Else
        MsgBox (ra.Row)
    End If
    Range("AD" & ra.Row).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="\\Network_share\users\New Quality NCR System\Returned NCR's\NCR " & NCR_No & ".xlsx", _
        TextToDisplay:="NCR SENT"