Hi Bob,

My guess would be a problem with the following line of code:

strNewRge = "=" & .Parent.Name & "!"

All manner of sheet tab names can require you to surround the name of the
worksheet in single quotes. If it's required and you don't do it then your
RefersTo string will be invalid. If it's not required and you include the
single quotes anyway, it won't hurt anything, so I always recommend
including them just to be safe. Try it like this instead:

strNewRge = "='" & .Parent.Name & "'!"

Note the single quote immediately after the equal sign and immediately
before the exclamation point.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Robert L. Porter" wrote in message
...
I have a macro in an Excel workbook which is intended to
automatically update some information from a master
template. This update may require resizing named ranges.
Based upon a Sams book (I don't have the book with me at
the moment so I can't give the reference) on VBA 5, I use
the following code:

With Range("RangeName")
strNewRge = "=" & .Parent.Name & "!"
strNewRge = strNewRge & Cells(.Row, .Column).Address _
& ":"
strNewRge = strNewRge & Cells(.Row, ColCount).Address
Names("RangeName").RefersTo = strNewRge
End With

When the last line is executed (the line
containing "RefersTo"), I get an error message.

"Run-time error '-214741848 (80010108)':
Method 'RefersTo' of object 'Name' failed"

Looking at the value of "strNewRge" shows it to contain
the correct new range limits, properly formatted for range
definition.

A search in the Microsoft KnowledgeBase for this error
number brings up a post-sp2 hotfix which is supposed to
fix this error; however, applying this hotfix does NOT fix
the error, and it still persists.

Environment: Windows 2000 SP2 (5.00.2195)
Excel 2002 (10.4302.4210) SP-2
Visual Basic 6.3, Version 9108, VBA: Retail 6.3.8863

Any suggestions?

Bob Porter