named range in
 

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

named range in
 

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

--
Kevin Backmann


"steve_doc" wrote:

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

named range in
 

even simpler (no need to select)

Sub NameCurrentRegion()

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


End Sub

--
Regards,
Tom Ogilvy



"Kevin B" wrote:

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

--
Kevin Backmann


"steve_doc" wrote:

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

named range in
 

thanks for both replies - both work

"Tom Ogilvy" wrote:

even simpler (no need to select)

Sub NameCurrentRegion()

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


End Sub

--
Regards,
Tom Ogilvy



"Kevin B" wrote:

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

--
Kevin Backmann


"steve_doc" wrote:

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

named range in
 

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

But I could be wrong?


"steve_doc" wrote:

thanks for both replies - both work

"Tom Ogilvy" wrote:

even simpler (no need to select)

Sub NameCurrentRegion()

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


End Sub

--
Regards,
Tom Ogilvy



"Kevin B" wrote:

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

--
Kevin Backmann


"steve_doc" wrote:

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

named range in
 

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

--
Regards,
Tom Ogilvy


"steve_doc" wrote:

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

But I could be wrong?


"steve_doc" wrote:

thanks for both replies - both work

"Tom Ogilvy" wrote:

even simpler (no need to select)

Sub NameCurrentRegion()

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


End Sub

--
Regards,
Tom Ogilvy



"Kevin B" wrote:

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

--
Kevin Backmann


"steve_doc" wrote:

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