Range Naming Macro, Please Help

Posted by Herb B. on November 06, 2001 5:37 AM

I originally posted the following:

I have a spreadsheet with many schedules and each schedule occupies a range of cells that will not change. I want to create Range Names for each schedule that I can use for navigating the spreadsheet and printing. The trick is that I want each Range to be named based on text in a cell located in each schedule. I want a macro to do this for me and whenever I evoke the macro the Range will be renamed based on the entry in the cell.

Well here is my attempt at a Macro to get this done so that someone might understand what I want:

Sub NameRanges()
With Sheets("Panels")
currentregion="$AA$13:$AX$52"
currentregion.name=activecell("AE13").value
currentregion="$AA$58:$AX$97
currentregion.name=activecell("AE58").value

I want to repeat this to name 50 or 60 distinct ranges in the worksheet based on the entry in a cell. Can this work?

Posted by Dan on November 06, 2001 6:34 AM

Not a VBA expert, but try something like this:

Sub NameRanges()
Dim RangeName as String
RangeName=Range("AE13").Value
ActiveWorkbook.Names.Add Name:=RangeName, RefersToR1C1:="=Panels!R13C27:R52C50"
End Sub

notes:
"RangeName" is the variable set to what the value of the specified cell is.
"RefersToR1C1" specifies what the range of cells should be.

To set another name, you can just set RangeName to what ever cell value you want for the next range, and change the "RefersToR1C1" to the next range values. HTH.

Posted by Herb B. on November 06, 2001 9:57 AM

Thanks. It works.

You may not be a VBA expert but your macro worked like a charm. Thanks again.