View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
JLGWhiz JLGWhiz is offline
external usenet poster
 
Posts: 3,986
Default Assing a Name to a Range

You have to tell it where.

ActiveWorkbook.Names.Add Name:="Region", RefersTo:="=Sheet1!$A$4:$E$17"

"ryguy7272" wrote:

I am not having much luck with the following code:

Range("A65000").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="Region"

The range is A4:E17, but will almost certainly change in the near future.
It fails on the last line. What am I doing wrong?

I need the named range for a chart, which I am building on the fly:
Dim myChtObj As ChartObject
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=30, Width:=800, Top:=250, Height:=500)

myChtObj.Chart.SetSourceData
Source:=Sheets("Region-Chart").Range("Region")
myChtObj.Chart.ChartType = xlColumnClustered
'etc., etc., etc., etc., etc., etc., etc.,

Thanks,
Ryan---

--
RyGuy