| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Named Range
Sub NewNamedRange(NR As String) 'Nr is sending a number like 2007 Dim aw As String 'used to name range Dim aw1 As String 'used for range parameters aw = "Sales" & NR aw1 = "=" & "sales " & NR & "!$a$2:$i$2" ActiveWorkbook.Names.Add Name:=aw, RefersTo:=aw1 End Sub What am I doing wrong? In the Immediate window sales= "sales" nr= "2007" aw1 = "=" & "sales " & NR & "!$a$2:$i$2" ?aw1 =sales 2007!$a$2:$i$2 aw1 is correct but the named range becomes this =sales '2007'!$A$2:$I$2 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Range
Try this
Sub NewNamedRange(NR As String) 'Nr is sending a number like 2007 Dim aw As String 'used to name range Dim aw1 As String 'used for range parameters aw = "Sales" & NR aw1 = "=" & "'sales " & NR & "'!$a$2:$i$2" ActiveWorkbook.Names.Add Name:=aw, RefersTo:=aw1 End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "C Brehm" wrote in message .. . Sub NewNamedRange(NR As String) 'Nr is sending a number like 2007 Dim aw As String 'used to name range Dim aw1 As String 'used for range parameters aw = "Sales" & NR aw1 = "=" & "sales " & NR & "!$a$2:$i$2" ActiveWorkbook.Names.Add Name:=aw, RefersTo:=aw1 End Sub What am I doing wrong? In the Immediate window sales= "sales" nr= "2007" aw1 = "=" & "sales " & NR & "!$a$2:$i$2" ?aw1 =sales 2007!$a$2:$i$2 aw1 is correct but the named range becomes this =sales '2007'!$A$2:$I$2 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Named Range
One way.
Sub makename() nr = 2007 Sheets("Sales " & nr).Range("a2:i2").Name = "Sales" & nr End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "C Brehm" wrote in message .. . Sub NewNamedRange(NR As String) 'Nr is sending a number like 2007 Dim aw As String 'used to name range Dim aw1 As String 'used for range parameters aw = "Sales" & NR aw1 = "=" & "sales " & NR & "!$a$2:$i$2" ActiveWorkbook.Names.Add Name:=aw, RefersTo:=aw1 End Sub What am I doing wrong? In the Immediate window sales= "sales" nr= "2007" aw1 = "=" & "sales " & NR & "!$a$2:$i$2" ?aw1 =sales 2007!$a$2:$i$2 aw1 is correct but the named range becomes this =sales '2007'!$A$2:$I$2 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
| automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Linear Mode
