#NAME when I insert a sheet from a template
 

I'm inserting a worksheet, and that worksheet is from a template file. So the code looks like

Set wks = ThisWorkbook.Worksheets.Add(NextSheet, , , "C:\MyTemplate.xlt"

This works well except for one thing: formulas in this new sheet that refer to named ranges give a #NAME error. If I click my mouse in the formula editor and press <Enter, magically the formula resolves properly. I've tried using Worksheet.Calculate and Application.Calculate, but they don't make any difference. Is there a way I can force these formulas to calcualate from code

I doesn't matter if the range is defined locally to the sheet or globally to the workbook

Thanks

James

#NAME when I insert a sheet from a template
 

Untested: Maybe you could do a replace (equal sign with equal sign) as the next
step in your code.


jstrater wrote:

I'm inserting a worksheet, and that worksheet is from a template file. So the code looks like:

Set wks = ThisWorkbook.Worksheets.Add(NextSheet, , , "C:\MyTemplate.xlt")

This works well except for one thing: formulas in this new sheet that refer to named ranges give a #NAME error. If I click my mouse in the formula editor and press <Enter, magically the formula resolves properly. I've tried using Worksheet.Calculate and Application.Calculate, but they don't make any difference. Is there a way I can force these formulas to calcualate from code?

I doesn't matter if the range is defined locally to the sheet or globally to the workbook.

Thanks,

James

--

Dave Peterson

#NAME when I insert a sheet from a template
 

try this

Application.CalculateFul

#NAME when I insert a sheet from a template
 

Yet another reason to hate Excel 97 - Application.CalculateFull doesn't exist. I'll transfer my project to my other machine and test it on XP, where I see the method does exist, but it doesn't do me any good today for this client. They really have to upgrade soon..

Thanks

James

#NAME when I insert a sheet from a template
 

Dave

That's a wild and crazy idea, but it sounds like it might work. To optimize I guess I'll have to do a global search/replace on .UsedRange, that would probably be the fastest

Thanks

James

#NAME when I insert a sheet from a template
 

I've never noticed any delay when I do it manually.

In xl2002, I hit ctrl-a, ctrl-h and type my stuff and hit ok.

The only damage that could happen is if you have a cell formatted as text and
have a value that begins with =, then excel will try to convert it to a formula.



jstrater wrote:

Dave,

That's a wild and crazy idea, but it sounds like it might work. To optimize I guess I'll have to do a global search/replace on .UsedRange, that would probably be the fastest.

Thanks,

James

--

Dave Peterson

#NAME when I insert a sheet from a template
 

Dave

It works just fine. Just to avoid replacing non-formula-starting equal signs I did a select special on formulas in the sheet

Thanks

James