VBA, Copy Cell formula to a defined Name

[Luluwai]

We have a cell A1 with the formula (say) "=[Pricelist2019v2.xls]Company!$A$1:$L$22000"

We want to use VBA to copy this to a Defined Name, (say) "RangeA".

We have tried may possible ways of doing this without success.
So suggestions appreciated as to how this may be accomplished!

 

[DanteAmor]

Try

Sub test2()
    Range("A1").Copy Range("RangeA")
End Sub

 

[Luluwai]

Many thanks, and I owe you and apology, in that I did not define the requirement exactly. I want to copy the actual formula in A1 into the Name "Range" as I need to use the name "Range" (and variation on this name) in a number of places (like some 20,000 places) in the workbook as one cell pivot tables.

Comments appreciated.

 

[DanteAmor]

I think I did not understand, but try this

Sub Macro2()
    ActiveWorkbook.Names("RangeA").RefersToR1C1 = Range("A1").Formula
End Sub

 

[Luluwai]

Hi, could not get it to work. In essence the Name "RangeA" has to contain the formula "=[Pricelist2019v2.xls]Company!$A$1:$L$22000" which will be used in a cell (88,000 to be exact) {=INDEX(rangeA,MATCH($C73&E$68,rangeA1&rangeA2,0),5)} etc.. And it takes some 5 hours to make the calculations.

I'm trying to build a foolproof workbook which, whenever we receive new spreadsheet, can be integrated into our normal 'working' spreadsheet by inexperienced users of same.