Copying worksheet - Macro referring to current sheet?

S

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,
 
C

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.
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Name a range using cell contents 2
switch to another worksheet, then return to original sheet 2
InputBox to name a range 2
Insert Named Range using Excel Macro 1
Duplicating worksheet based on range and changing worksheet name 3
Run macro from active workbook whose name keeps changing 20
Curious string error in named range automation attempt - just doesn'tseem to make sense! 5
Copying cells and formula from closed workbook to all sheets in open workbook 0

Top