Find and Replace WB references (VBA)

[BigBeachBananas]

For context, I have about 200 client files that are set up the same way. I created a new "final report" tab in one of the 200. Let's call this masterWB. Now I need to replicate that "final report" tab in the other 199. I have a macro that copies the "final report" tab into the others, but an issue I'm facing is that the formulas are still referencing the masterWB, but I need it to reference their respective workbook. I wrote some macro to find and replace it but it doesn't seem to be replacing the references at all. I'm open to suggestions and does not have to be this way.

Sub Macro1()
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim sourcePath As String
    Dim fileName As String
    Dim i As Integer

 
    Set wb1 = ThisWorkbook

 

    sourcePath = "S:\National Analytics\R&D\Models"
    fileName = Dir(sourcePath & "\*.xlsm")

 

    ' Loop through files in the sourcePath
'    Do While fileName <> ""
        ' Open the current file in the loop
        Set wb2 = Workbooks.Open(sourcePath & "\" & fileName)

 

        ' Copy the "SQL" sheet from wb1 to wb2
        wb1.Sheets("SQL").Copy After:=wb2.Sheets(wb2.Sheets.Count)

 

        ' Perform find and replace operations (You need to define this procedure)
        Lose_Copied_Named_Ranges
 

        ' Rename sheets in wb2 based on values in "SQL" sheet
        Dim SheetStart As Range
        Dim sheetName As String

 

        Set SheetStart = wb2.Sheets("SQL").Range("SheetStart")

      
        ' Replace references in the copied "SQL" sheet using Cells.Replace
        ' It is not replacing
        wb2.Sheets("SQL").Cells.Replace What:="['" & wb1.Name & "]'", _
            Replacement:="['" & wb2.Name & "]'", LookAt:=xlPart, SearchOrder:=xlByRows, _
            MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
            FormulaVersion:=xlReplaceFormula2


        For i = 1 To 10
            wb1.Sheets("SQL").Activate
            sheetName = SheetStart.Offset(i - 1, 0).Value
            wb2.Sheets(sheetName).Name = "Option " & i
            wb2.Sheets("Summary").Range("Sheet" & i).Value = "Option " & i
        Next i


        ' Save and close wb2 in sourcePath\Updated
        wb2.SaveAs sourcePath & "\Updated\" & fileName
        wb2.Close SaveChanges:=False


        ' Get the next file in the folder
        fileName = Dir
    Loops
End Sub

 Sub Lose_Copied_Named_Ranges()
    Dim xName As Name
    For Each xName In Application.ThisWorkbook.Names
        If InStr(1, xName.RefersTo, "'") > 0 Then xName.Delete
    Next xName
End Sub

Here's the most complicated formula in the sheet.

=INDEX(INDIRECT($B$2&$B$3),MATCH(Q$7,INDIRECT($B$2&$B$4),0),IF($U11=Incumbent,IF('Option 1'!N21="Yes",2,1),0)+MATCH($U11,INDIRECT($B$2&$B$5),0))

 

[BigBeachBananas]

The simpler questions. On a current active sheet. How can I remove all of the external references within the formula?

For example:

=INDEX('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!Q7:Z7,1,MATCH('[COE BENCHMARK - MASTER -AH 5.xlsm]Summary'!N34,Drop_Opt10,0))

How to remove the '[COE BENCHMARK - MASTER -AH 5. xlsm] and the apostrophe before the exclamation?

=INDEX(Summary!Q7:Z7,1,MATCH(Summary!N34,Drop_Opt10,0))

 

[Michael M]

Why not use Find/Replace
Select the used range
Find:
[COE BENCHMARK - MASTER -AH 5.xlsm]...with out the leading apostrophe
Replace:
leave blank
Then select replace all

 

[BigBeachBananas]

When I do this, the file browser pops up.

 

[Michael M]

when the browser pops up click cancel

 

[BigBeachBananas]

when the browser pops up click cancel

It seems to be doing it for every cell that has the external reference. What is the reason? (For my own understanding).

 

[Michael M]

Maybe UNTESTED

Sub MM1()
Application.DisplayAlerts = False
With activesheet.UsedRange
        .Replace What:="[COE BENCHMARK - MASTER -AH 5.xlsm]", Replacement _
        :="", LookAt:=xlPart, FormulaVersion:=xlReplaceFormula2
Application.DisplayAlerts = True
End Sub

 

[RoryA]

This appears to be a duplicate of this. Please do not post the same question more than once. This thread is now closed.