Function Question

[SteveDB1]

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.

 

[Tom Hutchins]

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

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]

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 =

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.

 

[Mark Bressler]

Hello,

I have columns of information like the one below on one worksheet. Based on
certain criteria, I want to copy these columns to another worksheet in the
same file. The "4" above John Smith's name would be the criteria. After
you glance at the info below, please scroll down for a little more info.

Column A

Row 1 4
Row 2 John Smith

Row 3 1

Row 4 1
Row 5 1

Right now, these columns show up on every other column in the original
worksheet and I want to paste them to every other column in the destination
worksheet. The rows are from B1 - B227. If necessary, I can change the
spreadsheets so the columns are next to each other.

Can anyone help me? If so, please email your response to
(e-mail address removed) because I'm not a regular here.

Thanks in advance.

 

[SteveDB1]

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

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

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.

 

[SteveDB1]

Yep, it works, now I've just to figure the code part out.
Dave, again-- thank you.

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 =

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.

 

[SteveDB1]

Mark, I have no idea what you're talking about.
I'd encourage you to take your post, and make your own topic, because it
doesn't seem to have anything to do with what I've posted on.
I wish you the best in finding a solution... these guys/gals here are really
helpful, and gracious.

 

[Dave Peterson]

Record a macro when you select all the cells on the new sheet and do the
edit|replace.

You'll have the code.

Yep, it works, now I've just to figure the code part out.
Dave, again-- thank you.

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 =

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.

 

[SteveDB1]

Thanks Dave.

Record a macro when you select all the cells on the new sheet and do the
edit|replace.

You'll have the code.

Yep, it works, now I've just to figure the code part out.
Dave, again-- thank you.

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.