After you've set up your template so all the formulas work ok, change all those
offending formulas to plain old text.

Select the range
edit|replace
what: =
with: $$$$$=
replace all

Then when you insert the sheet from the template, you can have your macro do an
extra step--change the $$$$$= back to =



SteveDB1 wrote:

morning all.
I have a template workbook (xltx) that I've configured with all of my
worksheet functions.
I then have a macro that I can import/copy specific worksheets over from my
template to an active workbook.
Presently, when I activate the macro to copy a worksheet over, it retains
the name of the template workbook in my worksheet functions.
I.e.,
what starts off as
=subtotal(109,ShtNm!A1:A100)
becomes
=subtotal(109,'C:\Documents and Settings\username\Application
Data\Microsoft\Templates\[FileNm.xltx]ShtNm'!A1:A100)

What I'd like to have is that the worksheet function just remain
=subtotal(109,ShtNm!A1:A100)

How can I accomplish that?
Or can I?

Your helps are appreciated.

One person responded when I'd initially posted this on the 4th-- last week.
I tried his recommendation using indirect().
Easiest way may be to rewrite your formulas to indirect references.
=subtotal(109,INDIRECT("ShtNm!A1:A100"))

It did not work. I.e., it still included the reference back to the original
workbook.
=subtotal(109,indirect('C:\Documents and Settings\username\Application
Data\Microsoft\Templates\[FileNm.xltx]ShtNm'!A1:A100))
which of course throws a #Value error.

Thank you for your helps.

--

Dave Peterson