Hi guys,
Currently I'm referencing an external named range in a formula for cell A2 and copying it down until the last row (shown below).
I have to manually open the workbook first to make sure it doesn't crash on me, and also some of the forumula's end up so long it won't accept it.
What would be the best VBA way of opening a connection to the workbook (read only) in the background and referencing a named range in that external workbook in formula's, then closing the connection?
Many thanks
Currently I'm referencing an external named range in a formula for cell A2 and copying it down until the last row (shown below).
Code:
With Worksheets("Sheet1")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("$A2").Formula = "=IF(SUMPRODUCT(--ISNUMBER(SEARCH('C:\Path\Filename.xlsm'!NamedRange,R2)))>0,TRUE,"""")"
.Range("$A2").AutoFill Destination:=Range("$A2:$A" & lastrow)
End WithI have to manually open the workbook first to make sure it doesn't crash on me, and also some of the forumula's end up so long it won't accept it.
What would be the best VBA way of opening a connection to the workbook (read only) in the background and referencing a named range in that external workbook in formula's, then closing the connection?
Many thanks