Multiple Range VBA and search/replace

[Celticfc]

Hi all,

I'm trying to enter array formula to 2x hidden sheets and then do a replacement (as the array is over 255 characters) and then break links so that I can have it all as values.

1) it only works for the first part of the range (a2:a150), any chance I can apply the macro to all ranges done in one go?
2) is there a way to tidy up the macro and narrow it down?

code:

Sub Macro2()
  Dim wb As Workbook
Set wb = Application.ActiveWorkbook
  
 'first sheet - multiple range

  Sheets("ltis").Range("a2:a150, e2:j150, au2:ax150, ch2:ci150, cl2:cm150").FormulaArray = _
"=IFERROR(INDEX('https:/PATH/[el.xlsx]formulationsTK'!$A:$CX,SMALL(IF('https:/PATH/[el.xlsx]formulationsTK'!$B:$B=filter,ROW($B:$B)),ROW()),COLUMN()),"""")"
    
Sheets("ltis").Cells.Replace What:="PATH", Replacement:= _
        "/klxs/sharepoint.com/sites/Shared Documents"

'second sheet - single range

Sheets("klpn").Range("a2:e150").FormulaArray = _
"=IFERROR(INDEX('https:/PATH/[el.xlsx]formulationsTK'!$A:$CX,SMALL(IF('https:/PATH/[el.xlsx]formulationsTK'!$B:$B=filter,ROW($B:$B)),ROW()),COLUMN()),"""")"

Sheets("klpn").Cells.Replace What:="PATH", Replacement:= _
        "/klxs/sharepoint.com/sites/Shared Documents"
        
        
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
    For Each link In wb.LinkSources(xlExcelLinks)
        wb.BreakLink link, xlLinkTypeExcelLinks

    Next link

End If
End Sub

 

[Celticfc]

Any support is appreciated...