select a range in a macro

I am trying to name a variable range in a macro. I have recorded the
actions, but it always references the range in R1C1 terms - which therefore
always selcts the exact same area, not the variable area. The code I am
using is below. I want to change the "ActiveWorkbook.Names......" line to
use the CurrentRegion I have selected in the line above. BUT i dont know
how. Any help gratefully accepted.
Thanks
Andrew


Sub Macro9()
' Range("A1").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="obook", RefersToR1C1:= _
"=production!R1C1:R144C28"
Range("A1").Select
End Sub

Selection.CurrentRegion.Name = "obook"


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Andrew" <[email protected]> wrote in message
news:[email protected]...
> I am trying to name a variable range in a macro. I have recorded the
> actions, but it always references the range in R1C1 terms - which
therefore
> always selcts the exact same area, not the variable area. The code I am
> using is below. I want to change the "ActiveWorkbook.Names......" line
to
> use the CurrentRegion I have selected in the line above. BUT i dont know
> how. Any help gratefully accepted.
> Thanks
> Andrew
>
>
> Sub Macro9()
> ' Range("A1").Select
> Selection.CurrentRegion.Select
> ActiveWorkbook.Names.Add Name:="obook", RefersToR1C1:= _
> "=production!R1C1:R144C28"
> Range("A1").Select
> End Sub

Thanks Bob - Fantastic!

"Bob Phillips" wrote:

> Selection.CurrentRegion.Name = "obook"
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Andrew" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to name a variable range in a macro. I have recorded the
> > actions, but it always references the range in R1C1 terms - which
> therefore
> > always selcts the exact same area, not the variable area. The code I am
> > using is below. I want to change the "ActiveWorkbook.Names......" line
> to
> > use the CurrentRegion I have selected in the line above. BUT i dont know
> > how. Any help gratefully accepted.
> > Thanks
> > Andrew
> >
> >
> > Sub Macro9()
> > ' Range("A1").Select
> > Selection.CurrentRegion.Select
> > ActiveWorkbook.Names.Add Name:="obook", RefersToR1C1:= _
> > "=production!R1C1:R144C28"
> > Range("A1").Select
> > End Sub
>
>
>

Thanks Bob - Fantastic

"Bob Phillips" wrote:

> Selection.CurrentRegion.Name = "obook"
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Andrew" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to name a variable range in a macro. I have recorded the
> > actions, but it always references the range in R1C1 terms - which
> therefore
> > always selcts the exact same area, not the variable area. The code I am
> > using is below. I want to change the "ActiveWorkbook.Names......" line
> to
> > use the CurrentRegion I have selected in the line above. BUT i dont know
> > how. Any help gratefully accepted.
> > Thanks
> > Andrew
> >
> >
> > Sub Macro9()
> > ' Range("A1").Select
> > Selection.CurrentRegion.Select
> > ActiveWorkbook.Names.Add Name:="obook", RefersToR1C1:= _
> > "=production!R1C1:R144C28"
> > Range("A1").Select
> > End Sub
>
>
>