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
|