named range in

[Guest]

Hi All

Hoping someone can point me in the right direction with this? I need to
modify the following statment to refer to the CurrentRegion.

ActiveWorkbook.Names.Add Name:="Renewal_Report", RefersToR1C1:= _
"=Reports!R5C1:R17C13"

as the Current region will change with every entry, the above is not
appropriate.

Any Idea's?
Thanks in Advance
Steve

 

[Guest]

The following code will work, but only if there is at least one blank row
between regions:

Sub NameCurrentRegion()

Selection.CurrentRegion.Select
Selection.Name = "MyRegion"

End Sub

 

[Guest]

even simpler (no need to select)

Sub NameCurrentRegion()

Worksheets("Reports").Range("A5").CurrentRegion = _
"Renewal_Report"


End Sub

 

[Guest]

thanks for both replies - both work

even simpler (no need to select)

Sub NameCurrentRegion()

Worksheets("Reports").Range("A5").CurrentRegion = _
"Renewal_Report"


End Sub

 

[Guest]

Tom I think your code should read
Worksheets("Reports").Range("A5").CurrentRegion.Name = _
"Renewal_Report"

But I could be wrong?

 

[Guest]

Yep - my typo, but looks like you understood. Sorry.