Macro to name a range

[spence]

Thanks for all your help Tom

using the macro recorder, i came up with this:

Sub NameRange()
Range("B7:L10").Select
ActiveWorkbook.Names.Add Name:="Daniel_40",
RefersToR1C1:= _
"='CHA SQ'!R7C2:R10C12"
End Sub

i am working with 2 sheets. sheet "CHA SQ" and "Sheet2"

in cell J6 of Sheet 2 i have the text B7 (coming from an
INDIRECT formula) and likewise in cell K6 of sheet 2 i
have the text L10. the range B7:L10 is on "CHA SQ"
i am needing to modify this recorded macro to be able to
name the range B7:L10 "Daniel_40" (B7:L10 may change
every month but the formulae will change. i need the
macro to name the range specified in the two cells. i
would use something like OFFSET, however the sheet CHA SQ
is emailed to me every month and i copy it and paste it
over top of the previous months sheet, and re-name a
dozen or so ranges every month (somewhat time
comsuming). i tried using the INDIRECT function in the
Refers To: section of the Define Names dialog box to
reference J6 and K6 in hopes of returning the named
range, but apparantley you cannot use INDIRECT in the
worksheet referring to the same range that the name is
referring to. i need to use INDIRECT in the worksheet
for other things so using it in the range's name is out
of the question i guess. all i need is to modify this
macro to get it to name B7:L10 (which may change every
month) so n ext month it may be B7:L9 but the cells that
contain that text are already changingdue to some other
formulae. I hope that wasn't too confusing, TIA if this
is possible.

 

[Tom Ogilvy]

Dim s1 as String
Dim s2 as String
s1 = Worksheets("Sheet2").Range("B7").Value
s2 = Worksheets("Sheet2").Range("K6").Value
Worksheets("Cha SQ").Range(s1 & ":" & s2).Name = "Daniel_40"


Regards,
Tom Ogilvy