In a VBA script I need to add the active worksheet name, to a named
range that the script is building.

This works fine:

'Assign a Name to the last cell used in column A
'select the very last used cell in a Column:

Range("A65536").End(xlUp).Select
'assign a Name to the selection
'Selection.Name = "Htestc!ChartColAend"

However, I need to this code to run on several sheets so I want to
define the worksheet name as a variable and then have the value of the
variable be part of the name.

I'm trying this:

Dim Name1
name1 = ActiveSheet.Name

Range("A65536").End(xlUp).Select


Selection.Name = "worksheets(""name1"")!ChartColAend"

and

Selection.Name = "worksheets(""name1"").value!ChartColAend"

To no avail.

I'm lost again, any help will be appreciated
Thanks
Robert

name1 = Activesheet.Name
Selection.Name = "'" & name1 & "'!ChartColAend"

--
Regards,
Tom Ogilvy


"(E-Mail Removed)" wrote:

> In a VBA script I need to add the active worksheet name, to a named
> range that the script is building.
>
> This works fine:
>
> 'Assign a Name to the last cell used in column A
> 'select the very last used cell in a Column:
>
> Range("A65536").End(xlUp).Select
> 'assign a Name to the selection
> 'Selection.Name = "Htestc!ChartColAend"
>
> However, I need to this code to run on several sheets so I want to
> define the worksheet name as a variable and then have the value of the
> variable be part of the name.
>
> I'm trying this:
>
> Dim Name1
> name1 = ActiveSheet.Name
>
> Range("A65536").End(xlUp).Select
>
>
> Selection.Name = "worksheets(""name1"")!ChartColAend"
>
> and
>
> Selection.Name = "worksheets(""name1"").value!ChartColAend"
>
> To no avail.
>
> I'm lost again, any help will be appreciated
> Thanks
> Robert
>
>

Thanks Tom, sometimes the simple things realy kick my but.

Tom Ogilvy wrote:
> name1 = Activesheet.Name
> Selection.Name = "'" & name1 & "'!ChartColAend"
>
> --
> Regards,
> Tom Ogilvy
>
>
> "(E-Mail Removed)" wrote:
>
> > In a VBA script I need to add the active worksheet name, to a named
> > range that the script is building.
> >
> > This works fine:
> >
> > 'Assign a Name to the last cell used in column A
> > 'select the very last used cell in a Column:
> >
> > Range("A65536").End(xlUp).Select
> > 'assign a Name to the selection
> > 'Selection.Name = "Htestc!ChartColAend"
> >
> > However, I need to this code to run on several sheets so I want to
> > define the worksheet name as a variable and then have the value of the
> > variable be part of the name.
> >
> > I'm trying this:
> >
> > Dim Name1
> > name1 = ActiveSheet.Name
> >
> > Range("A65536").End(xlUp).Select
> >
> >
> > Selection.Name = "worksheets(""name1"")!ChartColAend"
> >
> > and
> >
> > Selection.Name = "worksheets(""name1"").value!ChartColAend"
> >
> > To no avail.
> >
> > I'm lost again, any help will be appreciated
> > Thanks
> > Robert
> >
> >