Hello.

Yes I can see why its hard to resolve if I don't make it clear for you. So let me give you some more information.

Basically I've created a new workbook which I've created two tabs and copied over data from my main file called "Mainstay Master Template.Xlsm"

W = Mainstay Master
X = Mainstay Report.

In my main file. The Mainstay report (x) gets its information from the Mainstay master tab (w). Which is essentially what I want to reproduce in the new workbook.

When I copy over the formula's however. The mainstay report still has references to the Mainstay Master Template.xlsm. So I want the formula's in Mainstay report (x) to only reference the Mainstay master (W) tab in the new created workbook. So no links to the Mainstay Master template.xlsm file.

Think I've changed the code in red as per your last post, but not sure If I've totally changed as you suggested. It breaks on this line. 

linkSources = b.Sheets(1).linkSources

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")
Set linkSources = b.Sheets(1)


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.Sheets(1).linkSources
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master.xlsx") Then _
          x.Sheets.ChangeLink link, newLink, xlExcelLinks
     Next
     End If