Copying worksheet - Macro referring to current sheet?

[SB_2009]

I have a macro setup to insert a named range in a worksheet however when i
copied the worksheet the macro still referred to the original worksheet. I
have tried changing the macro to refer to active worksheet - this doesn't
error but likeise doesn't seem to insert the required named range either.
The macro (after i have changed the original sheet name from Sheet 1 to
Active Worksheet) is:

Range("B15:K15").Select
ActiveWorkbook.Names.Add Name:=Range("M2").Value, RefersToR1C1:= _
"=ActiveWorksheet!R15C2:R15C11"

Thanks,

 

[CurlyDave]

try,

Dim r As Range
Set r = Range("M2")

ThisWorkbook.Names.Add Name:=r, _
RefersTo:="=$A$1:$C$10", Visible:=True

The Cell M2 should have a different word than the other sheet, or it
will just overwrite it.

 

[Patrick Molloy]

ActiveSheet.Range("B15:K15").Name = "myname"

 

[Don Guillett]

This should do it. Excel will automatically add the sheet name for you.

Sub addname()
Range("B15:K15").Name = Range("M2").Value
End Sub