Thank you for coming back promptly on this.
Unfortunately the suggestions you made didn't work.
I'm still seeing the reference as per the below.
=VLOOKUP($B$4,'[Mainstay Master Template.xlsm]Mainstay Master'!$F:$BU,27,FALSE)
Here's what the codes looks like now.
Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
Dim 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\Reports\"
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
With x
Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Sitenames"
End With
newLink = mypath & "\Mainstay Master.xlsx"
linkSources = b.linkSources(1)
If IsArray(linkSources) Then
For Each link In linkSources
If InStr(link, "Mainstay Master.xlsx") Then _
b.ChangeLink link, newLink, 1
Next
End If