Creating Named range problem

[Billy B]

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.

 

[JLGWhiz]

Guessing the "My List" is a sheet name, then

Change fromg

sht = "My List"

To

Set sht = Sheets("My List")

 

[B Lynn B]

It's a little hard to tell exactly what your range is supposed to be from the
way you wrote the "RefersTo:" string. But say you wanted from $A$1 to the
last used row of column E, this would do it:

Dim myRow As Long
myRow = Sheets("My List").UsedRange.Rows.Count
ActiveWorkbook.Names.Add "KidsNames", _
"='My List'!$A$1:$E$" & myRow

Adjust as needed to get the range you were actually going for.

 

[Dave Peterson]

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).