Changing the link to a file within a cell based on another cell's value

[Vickylmcc]

Hi all

Am a regular reader and user of the advice on these boards, so am really hoping you can help.

I have a spreadsheet with a list of store names in column A. I then have formulas in cell H3 to M3 which pick up data from cells in a separate workbook within the same folder. The second workbook is named as a store number - ie. 344.xlsx. The store number in this example would be the value in Cell A3.

What I am trying to do is create a macro that will copy the formulas down through each row (and new rows when stores are added to list) and then change the workbook link in the formula to pick up the new workbook name (which will be the store number in cell A4 - for example).

Indirect formulas won't work as the store workbooks won't always be open. I am aware of the morefunc addin, but that is not an option as this workbook will be used by others who can't add that in.

What I was hoping for was the VBA code for a simple replace , so that in the formula ='C:\Users\Vicky\Desktop\Store tracker folder\[1408.xlsx]Store Tracker'!$M$71 - the 1408.xlsx can be replaced with, for example 567.xlsx. But the workbook name will always be based on a cell value.

I have tried but failed hopelessly at doing this using Replace in VBA. Any help most welcome!

 

[Vickylmcc]

Replying to my own post. Got something to work...

Sub Update()


Dim LRow As String
LRow = Range("H" & Rows.Count).End(xlUp).Row


SName = Range("A3").Value
Range("H3:M3").Copy


Range("H" & LRow).Select
ActiveCell.Offset(1, -7).Select
NName = ActiveCell.Value


ActiveCell.Offset(0, 7).Select
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
Selection.Replace What:=SName, Replacement:=NName, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False


End Sub

[End code]