You want a name that will refer to that formula--not hard code the address into
the refersto parm, right?

I recorded a macro when I did it manually and got this:

ActiveWorkbook.Names.Add Name:="KidsNames", _
RefersToR1C1:="=OFFSET('My List'!R1C1,0,0,COUNTA('My List'!C1),5)"

Notice that this used referstoR1C1.

You could also use:

ActiveWorkbook.Names.Add Name:="KidsNames", _
RefersTo:="=OFFSET('My List'!$a$1,0,0,COUNTA('My List'!$a:$a),5)"

======
ps. watch those colons in addresses ($a:$a).



Billy B wrote:

I am trying to create a dynamic named range in my Workbook Open event and the
debugger tells me there is a problem. I can't figure it out. Any help would
be appreciated.

Dim sht As Worksheet
sht = "My List"
ActiveWorkBook.Names.Add Name:="KidsNames"
RefersTo:=sht.Offset($A$1,0,0,CountA($A$A),5)

Thank you.

--

Dave Peterson