Naming a Range with VB

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

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.
 
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.
 
Thanks for your response.
-----Original Message-----
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





.
Click to expand...
 
Back
Top