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
Code: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