week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:= Range("B2").Value & "_" & week,
RefersToR1C1:="=" & week & "!R2C2"

or if it is relative (one column to the right) to the activecell then

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:= Activecell.Offset(0,1).Value & "_" & week, _
RefersToR1C1:="=" & week & "!R2C2"

--
HTH

Bob Phillips

"Nick Hodge" wrote in message
...
Johnny

try this

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie_" & week, RefersToR1C1:="=" & week &
"!R2C2"

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Johnny" wrote in message
oups.com...
VBA snippet:

week = InputBox("Enter the week number", week)
ActiveWorkbook.Names.Add Name:="Eddie", RefersToR1C1:="='11'!R2C2"

Two things I need to do:

1. Substitute week for '11' (having syntax problem).
2. AND week with Eddie (for example =" "Eddie" & week")