VBA Edit Links

[SteveABC]

Hiya,

Need some help on my coding below.

I'm trying to edit the links when the file copies over the formulas and change the source book from the xlsm file to the xlsx. I've attempted the coding but it has no impact when I play through the code, which would indicate that I'm missing something or its not in the right order, but I simply can't figure out where I'm going wrong. Any ideas?

I've highlighted the code where I want to edit the links

Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As RangeDim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting"
With Sheets("Control")
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
  End With
Set w = Sheets("Mainstay Master")
Set X = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")

ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng

With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
X.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"

Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
X.Range("b5").PasteSpecial Paste:=xlPasteValues

newLink = ThisWorkbook.Path & "\Mainstay Master.xlsx"
linkSources = ThisWorkbook.linkSources(xlLinkTypeExcelinks)
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master Template.xlsm") Then _
          ThisWookbook.ChangeLink link, newLink, xlLinkTypeExcelLinks
     Next
     End If

With X
    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
     End With