Hi Tom,
Thanks for your response.
I apologize for not responding yesterday, I was working on another macro
that took all my attention, and I'd completely forgotten about this post
until I got home last night and saw the email reminder. Yes, I finally got it
to work, thanks to Jim Cone, and believe it or not-- the developers help file
for/in Excel.

I've got a question about your code.
First, that's pretty slick. This is the fifth straight day I've learned
something new about VBA. Thank you.

I tried it and found that the open file dialogue window opens. I'm guessing
because I've called to a file that's in my template folder.
Is there something that will prevent that from opening, so I don't have to
keep clicking cancel each time it activates? It ends up being 3 or 4 times
that the dialogue activates and requires cancellation.

Next, because the sheet names that are being called to in my subtotal eq's
differ from file to file, this has now got me wondering how to rename the
sheetname element within the equation, using VBA.

I know..... when will it ever end..... sigh. ;-)

It almost seems that I should use an input box, but I'd like something that
doesn't require my input. whooop, there I go gettin' lazy again. All this
darn programming has set my fingers to mush, while my brain keeps getting
stronger. Scary premise....





"Tom Hutchins" wrote:

After importing/copying the formulas from your template, you could select all
the cells in the target worksheet and do a Replace do get rid of the template
reference:

Const TmpltPath = "C:\Documents and Settings\username\Application
Data\Microsoft\Templates\[FileNm.xltx]"
Cells.Select
Selection.Replace What:=TmpltPath, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False

Hope this helps,

Hutch

"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.