Defining a range name w/ VBA

Posted by Paul Johnson on February 07, 2002 11:35 AM

I am using the code below to define a named range. The range appears in the name dialog box in Excel, but is not available via the F5 (Goto) or in formulas .... Any suggestions ?

Sub Prod_mgr_Range()
'Define Product Mgr Range
Windows("DP_Reporting.xls").Activate
Sheets("Admin").Select
Sheets("Admin").Range("j1").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="Prod_Mgr", _
RefersTo:="=Monthly Data!" & Range(ActiveCell, ActiveCell.End(xlDown)).Address

End Sub

Posted by Ivan F Moala on February 07, 2002 6:41 PM

Last line should read;

ActiveWorkbook.Names.Add Name:="Prod_Mgr", _
RefersTo:="='Monthly Data'!" & Range(ActiveCell, ActiveCell.End(xlDown)).Address

Ivan