VBA, Copy Cell formula to a defined Name

Luluwai

New Member
Joined
Aug 29, 2008
Messages
9
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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,044
Office Version
2007
Platform
Windows
Try

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

Luluwai

New Member
Joined
Aug 29, 2008
Messages
9
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,044
Office Version
2007
Platform
Windows
I think I did not understand, but try this

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

Luluwai

New Member
Joined
Aug 29, 2008
Messages
9
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,095,173
Messages
5,442,825
Members
405,199
Latest member
mkarnout

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top