L
LABKHAND
Hello All,
My main purpose is to have a VB code that upon pressing a button, recreates
all my named ranges automatically. 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) string
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
My main purpose is to have a VB code that upon pressing a button, recreates
all my named ranges automatically. 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) string
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