Dave Peterson formulated the question :
On the other hand, it only requires a single change to a worksheet name --
which makes it simpler <bg.

True enough!<BG

However, I don't think the OP was looking for a way to create defined
names for ranges via VBA. My understanding is he had a problem
referencing defined names he had already created. The context of my
replies has been to use local scope names and provide the correct
syntax to be used in the defined name dialog. I also gave some
suggestions for refering to these names from different sheets than they
were defined on. In all cases, the task involves manually entering the
name and so makes sense to do this in the defined name dialog at
worksheet design time.

I'd reserve using your suggestion for runtime usage where, for example,
I might be updating worksheets in an app workbook with new names. In
cases where a list of names need be updated in a multi-sheet workbook I
use something like Rob Bovey's table-driven approach to UI worksheet
management (Ch5 Professional Excel Development: Application Specific
Add-ins) for which names to be added to which sheets of the app
workbook. This is an awesome dev tool that, since implementing it, I
can't imagine developing anything without it.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc