Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranges, adding a column to a named print range then saving the file | Excel Programming | |||
Adding named range gives error "method range of object _Global failed " | Excel Programming | |||
Adding a named range to a cell | Excel Programming | |||
Adding to a named range on another worksheet | Excel Programming | |||
need help adding named range as new series to a graph | Excel Programming |