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

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

Hi Wayne,

Try:

Sub Macro1()
Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:H1")
Range(rng, rng.End(xlDown)).Name = "rngTest4"
End Sub


---
Regards,
Norman



"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

Wayne,

Or you could try this:


Dim sAddress As String
Sheets("DataList").Select
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
sAddress = ActiveWindow.RangeSelection.Address
Range(sAddress).Name = "Sheet1!rngTest"




Charle

--
Charle
-----------------------------------------------------------------------
Charles's Profile: http://www.excelforum.com/member.php...nfo&userid=601
View this thread: http://www.excelforum.com/showthread.php?threadid=31919