View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tom Hutchins Tom Hutchins is offline
external usenet poster
 
Posts: 1,069
Default Function Question

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.