Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create Name Ranges!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create Name Ranges!
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. -- Rick (MVP - Excel) "LABKHAND" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create 3 Ranges in 1 | Excel Discussion (Misc queries) | |||
Create a collection of ranges | Excel Programming | |||
Create a collection of ranges | Excel Programming | |||
Create named ranges | Excel Programming | |||
Using VB to to create named ranges | Excel Programming |