Macro to create Name Ranges!

[LABKHAND]

Hello All,

My main purpose is to have a VB code that upon pressing a button, recreates
all my named ranges automatically for all sheets in my workbook. I
appreciate all your help in this matter. Here is my problem:

Using Excel 2003: Let's say, in the CONSTANT sheet, I have a cell A1 which
contains a reference to a cell location of another sheet in the same
workbook. I am trying to create a name range in the VB code.

Let's say Constant!A1 contains the ('='FY2009-FSA Health Tracker'!A2) value.
In my VB code, I have the following summerized code:
Dim strRefersTo As String
{ I have code to select the CONSTANT sheet here....}
Then I have:
strRefersTo = Activesheet.Range("A1").value 'this string should have the
('='FY2009-FSA Health Tracker'!A2) value at this point.

Next I tried to assign it to a name range:
ActiveWorkbook.Names.Add Name:="TEST", RefersToR1C1:=strRefersTo

The code works, but when I check the ReferTo section of the TEST name range
in excel (INSERT->NAME->Define), it has the

value of: ='FY2009-FSA Health Tracker'!'A2'
Instead of: ='FY2009-FSA Health Tracker'!A2

How can I make this work?

Thanks

 

[Rick Rothstein]

Try using RefersTo instead of RefersToR1C1.

Also consider using an absolute reference for your A2 cell reference (to fix
the cell reference in the Name assignment) when assigning the string value
to the strRefersTo variable.