You can't use A1 notation in an R1C1 formula, and you have a spurious '

ActiveWorkbook.Names.Add Name:="PivotRange", RefersTo:= _
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) "


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Richard" wrote in message
...
I'm trying to define a named range in VBA using the following syntax.
However, it gives me an error regarding the equal sign and if I remove the
equal sign, then the named range is defined but it treats the formula as a
text value so it doesn't evaluate it. I'm sure there's something easy
that
I'm overlooking but what is it?

Any help is appreciated.

ActiveWorkbook.Names.Add Name:="PivotRange", RefersToR1C1:= _
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Shee1!A:A),10) '"

--
Richard