Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Question
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Question
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Function Question
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 because I'm not a regular here. Thanks in advance. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Question
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Question
Yep, it works, now I've just to figure the code part out.
Dave, again-- thank you. "Dave Peterson" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Function Question
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. "Mark Bressler" wrote: 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 because I'm not a regular here. Thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Question
Record a macro when you select all the cells on the new sheet and do the
edit|replace. You'll have the code. SteveDB1 wrote: Yep, it works, now I've just to figure the code part out. Dave, again-- thank you. "Dave Peterson" wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Question
Thanks Dave.
"Dave Peterson" wrote: Record a macro when you select all the cells on the new sheet and do the edit|replace. You'll have the code. SteveDB1 wrote: Yep, it works, now I've just to figure the code part out. Dave, again-- thank you. "Dave Peterson" wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Function Question | Excel Worksheet Functions | |||
question about ABS Function | Excel Worksheet Functions | |||
Function question | Excel Worksheet Functions | |||
Function question | New Users to Excel | |||
Function question | Excel Worksheet Functions |