VBA to define named ranges

[byrdamy]

I am looking to create vba that will define named ranges automatically. I would like to create a worksheet with columns that have the information needed to define the ranges,example: one column would have the desired name of the range, another column would have cell reference that I want the range to refer to

Range Name Refers to
RMEX DET AX1:AX25
RMAP DET Ay1:Ay25


I would then like to add this macro to the workbooks along with the required worksheet and run the macro, to create the named ranges automatically.

I rolled out a report to users they made it specific for their site and now I need to change something that would otherwise require them to have to reenter a ton of information. thanks.

 

[Scott Huish]

Something like this perhaps, although you can't have spaces in named ranges, so my code changes the spaces to underscores:

Sub TEST()
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    ThisWorkbook.Names.Add Replace(c, " ", "_"), "'" & ActiveSheet.Name & "'!" & c.Offset(, 1)
Next
End Sub

 

[mikerickson]

I'd assign the name with the .Name property of a range rather than the .Add method.

Range("AX1:AX25").Name = "RMEX_DET"

No matter which technique for assigning the name, my biggest concern would be that both the name and the address that the user entered was legal.