Wayne

One way:

Dim sAddress As String
sAddress = Sheets("DataList").UsedRange.Address
ActiveWorkbook.Names.Add Name:="rngTest", _
RefersTo:="=DataList!" & sAddress

Or just:

ActiveWorkbook.Names.Add Name:="rngTest", _
RefersTo:="=DataList!" & _
Sheets("DataList").UsedRange.Address

Regards

Trevor


"Wayne Huxman" wrote in message
...
I need to dynamically define a range name based on the current monthly
data
in a specified sheet. I would need to use the same range name multiple
times. I've tried the following (amoung others), but it doesn't seem to
work. Does anyone have any suggestions? Any help would be appreciated.

Sub Macro1()
'
Sheets("DataList").Select
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Dim sAddress As String
sAddress = ActiveWindow.RangeSelection.Address


ActiveWorkbook.Names.Add Name:="rngTest",
RefersToR1C1:="=ActiveWindow.RangeSelection.Addres s"


End Sub

Thanks in advance.

Best Regards,

Wayne