Addition required - 1) copying existing file from same directory as active workbook to new folder structure 2) updating hyperlink to new location

[LT1511]

Good morning all, Based on the help of an expert I have managed to create the following code that executes nicely what I am asking to do i.e. 1) create a new folder and subfolder structure at the same level as the active workbook path, based on the name in Column A (Column A is a CONCATENATE of Cx_Bx where X is Row) 2) create a hyperlink in Column G based on the entry in Column A Code can be found below (this works on both Mac and Windows!)

​

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(3)) Is Nothing Then
    Dim tr As String
    With Target
        tr = ThisWorkbook.Path & Application.Pathseparator & .Offset(, -2).Value  
            MkDir tr
            MkDir tr & Application.Pathseparator & "folder 1"
            MkDir tr &  Application.Pathseparator & "folder 2"
            MkDir tr & Application.Pathseparator & "folder 2" & Application.Pathseparator & "Sub-subfolder 1"
            .Hyperlinks.Add .Offset(, 4), tr
    End With
End If
End Sub

Now here comes the part I am trying to figure out. What I would like to do within the code above is insert a few line(s) that do the following 1) copy into the new Dir "Tr" a pre-existing file that is at the same directory as the active workbook path. 2)There is a chance that under certain conditions, I might be moving certain newly generated folders (and their associated subfolders) in another Folder Named Archive. Given this, What I am trying to figure out is what would be the code that might work on BOTH Mac and Windows on either of the below: a) auto-updating the hyperlink when the "Tr" folder is moved into the Archive selection? b) if a link is not working - opening a Browser window to navigate to the new location and select the new folder which would then auto-update the hyperlink? My fear with this is that it cannot be coded in a way that works both on Mac and WindowsAny ideas?Luke

PS: Link to post asked in different forum: http://stackoverflow.com/questions/...ting-file-from-same-directory-as-active-workb
PPS:I should probably also say that if it cannot be within the same sub, would be happy to run it as a separate Sub BUT only AFTER the existing sub is executed​

 

[LT1511]

Ideally and with regards to point 1 above: It would also be nice to rename this .xlsm file to a new name that is "TR".XLSm (Basically, the name of the file should be the same as the name of the new folder). But I do not know if this can be done across both Mac and Win - but any suggestions welcome

 

[LT1511]

any ideas?