VBA update links

kris01

New Member
Joined
May 29, 2019
Messages
6
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
Did you put a breakpoint inside the For Each link loop? (and examine what is going on, by using Watches and/or Immediate Window ... while stepping through one line of code at a time?)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top