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

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

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