Stef here is a reply i got many months ago from an MVP on this forum.
The code was NOT written by me.
Try something like the following:
If being done from VBA in Excel
Workbooks("Book1.xls").Names.Add Name:="test",
RefersTo:="=sheet1!$a$1:$c$20"
If being done from VBA in any application other than Excel
<ApplicationObject.Workbooks("Book1.xls").Names.A dd Name:="test",
RefersTo:="=sheet1!$a$1:$c$20"
Where <ApplicationObject is the variable that refers to the Excel
Application instance
Easier would be:
rnum = 21
With xlwksht2
.Range("A3:A" & rnum).Name = Name1
End With
Hope this is of help
--
Message posted from
http://www.ExcelForum.com