adding worksheet name to a named range with VBA?
 

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

adding worksheet name to a named range with VBA?
 

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

--
Regards,
Tom Ogilvy


" 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

adding worksheet name to a named range with VBA?
 

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

Tom Ogilvy wrote:
name1 = Activesheet.Name
Selection.Name = "'" & name1 & "'!ChartColAend"

--
Regards,
Tom Ogilvy


" 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