ActiveWorkbook.Names.Add Name:=CurrentRange,
RefersToR1C1:= _
"=OFFSET('" & Activesheet.Name & "'!$A$1,0,0,
COUNTA('" & ActiveSheet.Name & "'!$A$1:$A$365),1)"

Note the addition of single quotes on each side of ActiveSheet.Name since
the sheet name could contain spaces.

--
Regards,
Tom Ogilvy


"Justin" wrote in message
...
I have a loop that I run through where the name of the
CurrentRange variable changes each time through.

For example, if the CurrentRange variable is "Justin's
Range" and the CurrentSheet variable is "Justin's Sheet",

I want to name the range, "Justin's Range", with the
following formula:

"=OFFSET("Justin's Sheet"!$A$1,0,0,
COUNTA("Justin's Sheet"!$A$1:$A$365),1)"

This is the code I'm using:

ActiveWorkbook.Names.Add Name:=CurrentRange,
RefersToR1C1:= _
"=OFFSET(CurrentSheet!$A$1,0,0,
COUNTA(CurrentSheet!$A$1:$A$365),1)"

I think it's trying to find a sheet called "CurrentSheet"
instead of computing the formula out.

Any help would be appreciated.