Macro to Paste Range Names

Question

So far I have this.

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("E3").Select
    ActiveWorkbook.Names.Add Name:="CGSF.COMUTIL.2", RefersToR1C1:= _
        "=Sheet2!R3C5"
    ActiveWorkbook.Names("CGSF.COMUTIL.2").Comment = ""
    Range("F3").Select
    ActiveWorkbook.Names.Add Name:="CGSF.DISTRATE.2", RefersToR1C1:= _
        "=Sheet2!R3C6"
    ActiveWorkbook.Names("CGSF.DISTRATE.2").Comment = ""
    Range("G3").Select
    ActiveWorkbook.Names.Add Name:="CGSF.CC.2", RefersToR1C1:="=Sheet2!R3C7"
    ActiveWorkbook.Names("CGSF.CC.2").Comment = ""
    Range("H3").Select
    ActiveWorkbook.Names.Add Name:="CGSF.FUNCTION.2", RefersToR1C1:= _
        "=Sheet2!R3C8"
    ActiveWorkbook.Names("CGSF.FUNCTION.2").Comment = ""
End Sub

What I would like is to be able to provide a start column and an end column and then have the macro take the cell name and make it the range name for that cell.

Also is there a way to have add name:="" pull from the cell value instead of having to spell it out?

Do you want to recreate the named ranges in another workbook or just print the names in another workbook or both? — Ravi Yenugu, Aug 14 '14 at 19:18
*Is it possible to put together a macro to take a list of names from one excel book and use those names to make range names in another workbook in a row consecutively.* **YES**. Now, what is your *actual* question or problem? — David Zemens, Aug 14 '14 at 19:23

score 0 · Answer 1 · answered Aug 14 '14 at 20:21

Assuming you have cell values in Sheet2 from E3 to H3 cells that needed to be converted into Named Ranges

Sub CellValue_To_NamedRange()
Dim a As Range, MyName As Range

Set a = Sheets(2).Range("E3:H3")

For Each MyName In a
 ActiveWorkbook.Names.Add Name:=MyName, RefersTo:=MyName
Next

End Sub

Also, From Documentation there is not way to add comments to named ranges through vba

Macro to Paste Range Names

1 Answers1