Code for Hyperlink

[jrtdcg]

Hi All,
I've been doing some searching regarding hyperlinks, but can't seem to find a near match for what I'm trying to accomplish.

I have a workbook that is generating a review process from the current user in the workbook. The process involves a lot of different steps. Everything seem to be working well within the review process except for the hyperlink. What I'd like to do is have the code look at the current path and provide a hyperlink to a cell within the spreadsheet. After the workbook has been renamed from the review process the next user has a direct like from the previous workbook. The problem that I'm having now is my code only show a link to the folder and the link won't open. I'd like for it to show the entire path directly to the file so that the file will open when it hyperlink is clicked.

Currently showing up in cell D1:

S:\Sales_Data\Q2014\010186-10901 Roosevelt Island- S046FD0- XX SEPS- D XXXXX\Latest Revision to Customer This is what should be showing up in cell D1: S:\Sales_Data\Q2014\010186-10901 Roosevelt Island- S046FD0- XX SEPS- D XXXXX\Latest Revision to Customer\10186-10901.xlsm <tbody> </tbody>

<tbody>
</tbody>

<tbody>
</tbody>

See below:

'--------------------This is where I'm currently putting the filename path 
Range("D1").Select
        ActiveCell.FormulaR1C1 = ActiveWorkbook.Path
    
   
'---------------This where I copy from one workbook to the other workbook and add the hyperlink

    Windows("Approval_Review.xlsm").Activate
Sheets("Bill of Material").Select
    Range("D1").Select
    Selection.Copy
     Windows("book1.xlsx").Activate
    Range("C40").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If cell <> "" Then
ActiveSheet.Hyperlinks.Add cell, cell.Value
End If
Next

Any help would be appreciated....Thanks in advance!!!

J.R.

 

[jrtdcg]

I think I have problem identified...now. Just not sure of the fix. The hyperlink that is copied from one workbook to the other workbook works fine. It appears that the link is lost when the new workbook is saved under another name. In my code I copy and paste then rename the file.