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