Assigning name to current region

[KDJ]

Hello

After selecting the current region, I would like to assign the name
Total_Statistic. The region can change and the name should be overwritten
each time I run the macro.

I am selecting the region as follows:

Set tbl = ActiveCell.CurrentRegion
tbl.Select

I can't find the right VBA command to assign a name, given that this isn't a
cell reference. Would really appreciate some help!
--
Thanks very much. KDJ

[Norman Jones]

Hi KDJ,

Try something like:

Public Sub Tester03a()
Dim tbl As Range

Set tbl = ActiveCell.CurrentRegion()

ActiveWorkbook.Names.Add _
Name:="Total_Statistic", _
RefersTo:=tbl
End Sub

---
Regards,
Norman



"KDJ" <[email protected]> wrote in message
news:[email protected]...
> Hello
>
> After selecting the current region, I would like to assign the name
> Total_Statistic. The region can change and the name should be overwritten
> each time I run the macro.
>
> I am selecting the region as follows:
>
> Set tbl = ActiveCell.CurrentRegion
> tbl.Select
>
> I can't find the right VBA command to assign a name, given that this isn't
> a
> cell reference. Would really appreciate some help!
> --
> Thanks very much. KDJ

[KDJ]

Thanks again, Norman ... you've saved my Monday

KDJ


"Norman Jones" schrieb:

>
> Hi KDJ,
>
> Try something like:
>
> Public Sub Tester03a()
> Dim tbl As Range
>
> Set tbl = ActiveCell.CurrentRegion()
>
> ActiveWorkbook.Names.Add _
> Name:="Total_Statistic", _
> RefersTo:=tbl
> End Sub
>
> ---
> Regards,
> Norman
>
>
>
> "KDJ" <[email protected]> wrote in message
> news:[email protected]...
> > Hello
> >
> > After selecting the current region, I would like to assign the name
> > Total_Statistic. The region can change and the name should be overwritten
> > each time I run the macro.
> >
> > I am selecting the region as follows:
> >
> > Set tbl = ActiveCell.CurrentRegion
> > tbl.Select
> >
> > I can't find the right VBA command to assign a name, given that this isn't
> > a
> > cell reference. Would really appreciate some help!
> > --
> > Thanks very much. KDJ
>
>
>