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.

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

Change fromg

sht = "My List"

To

Set sht = Sheets("My List")




"Billy B" <(E-Mail Removed)> wrote in message
news:23DA362C-00B7-4887-95E6-(E-Mail Removed)...
>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.

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.

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

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