$$Excel-Macros$$ Need to copy of 1st sheet of every workbook in a single work book
Dear all, I have many work book in a single folder. I need a macro which if I run will copy the 1st sheet of every workbook in a single workbook (though as separate sheet). Like if I have 2 workbook viz WB1 and WB2 then I want the 1st worksheet of WB1 as 1st worksheet in a new workbook (say WB3) and again 1st worksheet of WB2 as 2nd worksheet of WB3. Thanks in advance. Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Need to copy of 1st sheet of every workbook in a single work book
Hi Sandeep, Save this attachment in the folder where you have all the workbooks and try to run the macro (Button) on page 1 of this file. Hope this helps, Mrinal On Wed, Nov 2, 2011 at 2:08 PM, chhajersand...@gmail.com wrote: Dear all, I have many work book in a single folder. I need a macro which if I run will copy the 1st sheet of every workbook in a single workbook (though as separate sheet). Like if I have 2 workbook viz WB1 and WB2 then I want the 1st worksheet of WB1 as 1st worksheet in a new workbook (say WB3) and again 1st worksheet of WB2 as 2nd worksheet of WB3. Thanks in advance. Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com test.xlsm Description: Binary data
Re: $$Excel-Macros$$ Most Helpful Member Oct'11 - Sam Mathai Chacko
Congratulations Sam, you are wonderful. Regards, DILIPandey On 2 Nov 2011 01:20, Sam Mathai Chacko samde...@gmail.com wrote: Dear Noorain, Dear Haseeb, Thank you. Noorain, you've already been there. Have learned things from you. Haseeb, you are a gem of a formula champion. thanks for all the learning. Noorain, thank you for posting at the domain page also. For all well wishers, thank you for regarding my time. Just to ensure I don't misuse post count, I'll say a blanket thanks to all. And I hope my presence here makes some difference to the excel enthusiasts in the forum. Happy excelling and learning to EVERYBODY. Regards, Sam On 02/11/2011, Haseeb Avarakkan haseeb.avarak...@gmail.com wrote: Congratulations Sam -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Sent from my mobile device Sam Mathai Chacko -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Number Formats in French
Hi Sam, I see your point about using the other formatting conditions; it turns all my significant values after the decimal place to zero. I have gone through our correspondences and have uncovered this format you previously suggested: # ### ### ### ###\,00 I have two general issues that I can't seem to resolve. When I use this format I lose the significance of my figures. As an example if I have $0.54 (which is in millions) and I use the above format it converts the value to ,01 which is not correct. The other part is that I can't get a leading zero if the value is less than a whole number: English - $0.25 French - 0,25 When I change the format to equal: # ### ### ### ###\0,00 to provide a leading zero it converts whole numbers to zero and that is not correct either. Any help you can provide will be super! I really appreciate your patience with this issue. Thank you! Kent From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Sam Mathai Chacko Sent: October 31, 2011 11:12 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Number Formats in French Which is what I was point out all along in my previous mails Kent. You'll have to use the other format that I suggested. With the format you are using, it will always show a ,00 in the end, instead of the decimal values. And you'll have to divide in by 10,000 instead of 1,000,000 just to compensate for the work around. Regards, Sam On Mon, Oct 31, 2011 at 8:36 PM, Boucher, Kent C kent.bouc...@tbs-sct.gc.camailto:kent.bouc...@tbs-sct.gc.ca wrote: The format that you provided is really good but I have one other issue. What I have been doing is taking a very large number (in the millions) and I have reducing by millions and then rounding. Here is an example: English accounting: 1) $4,333,222 - =round((4,333,222/1,000,000),2) 2) To give me = $4.33 in Million. French Accounting: When I convert the French accounting using the provided format code; this is what I get: 1) 4 333 222 - =round((4333222/100),2) 2) It gives me = 4,00 in Million. Which is inconsistent with the English but the English is correct in terms of how I need to illustrate the numbers. I should have said that I was rounding down to small numbers but I didn't think at the time it was necessary. Just to be clear: It would be IDEAL if the results for the French could be equal to and formatted like: 4,33 Million. You guys have been very helpful - much appreciated! Thank you, Kent From: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.commailto:excel-macros@googlegroups.com] On Behalf Of B.N.Chethan Kumar Sent: October 28, 2011 5:52 PM To: excel-macros@googlegroups.commailto:excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Number Formats in French Thanks SAM, I missed that part. please find the updated format. Regards Chethan Kumar BN On Sat, Oct 29, 2011 at 3:15 AM, Sam Mathai Chacko samde...@gmail.commailto:samde...@gmail.com wrote: The OP had requested for the decimal separator to be a comma (,) Sam On Sat, Oct 29, 2011 at 3:07 AM, B.N.Chethan Kumar chetankumar1...@gmail.commailto:chetankumar1...@gmail.com wrote: Hi Kent, I have attached the file with custom format. Kindly check if these help. Regards Chethan Kumar BN On Fri, Oct 28, 2011 at 9:57 PM, HarryP Knuckles kent.bouc...@tbs-sct.gc.camailto:kent.bouc...@tbs-sct.gc.ca wrote: Hi guys! I have a quick question regarding formats. I have to convert english accounting procedures into French accounting procedures. In English one thousand dollars is equal to $1,000.00. In French one thousand dollars is equal to 1 000,00. the difference is that the comma before a thousand is replaced with a space AND our decimal is replaced with a comma and NO dollar sign. Another example is: In English one thousand dollars is equal to $9,555,333.00. In French one thousand dollars is equal to 9 555 333,00. Can you help me create this custom format? Your help is great appreciated!! Thank you, Kent -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this
$$Excel-Macros$$ Re: Test Cell String for Element match and report Accuracy
So, no takers huh ? .. Anyone .. ? -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Diffrent Sheets to Create from one $$$$$$$$$$$$$$$$$$$$$$$$$
Can anyone help me From: shashank bhosle catchshashankbho...@yahoo.co.in To: excel-macros@googlegroups.com excel-macros@googlegroups.com Sent: Wednesday, 2 November 2011 9:28 PM Subject: Diffrent Sheets to Create from one $ I have an INDI sheet which includes details of each member I want to make a different workbooks for each of them with the details of each member First 3 columns are common for each member and remaining columns from E to Q changes according to member name in cell E13,R13 respectivly There are more than 1000 emp name in my sheet . i recorded a code but it doesn’t Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+a ' Range(A1:G12).Select Selection.Copy Workbooks.Add ActiveSheet.Paste ThisFile = Range(D1).Value ActiveWorkbook.SaveAs Filename:=ThisFile Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Columns(D:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:G12).Select Selection.Copy Workbooks.Add Selection.Font.Bold = True Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Selection.Copy Windows(Book2).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal ActiveSheet.Paste ThisFile = Range(D1).Value ActiveWorkbook.SaveAs Filename:=ThisFile Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Columns(D:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:G12).Select Selection.Copy Windows(Book2).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Workbooks.Add ActiveSheet.Paste Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Range(C19).Select End Sub Plz suggest Thanks and Regards Shashank S Bhosle -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Help
Mahreen, I am getting 1 and -2 as answers, and after looking at the
formula, I don't think there's anything wrong with the result. I am also
able to select NA, or the Scores. Are you facing some other problem? If
yes, please explain.
Regards,
Sam Mathai Chacko
On Wed, Nov 2, 2011 at 7:29 PM, Mahreen Ellahi mahreen.acc...@gmail.comwrote:
Thanks both of u for the solution :-) I have different cells in my sheet
which are to be added which includes both text and number. I am not able
generate result with these formulas. Once again help needed.
On Wed, Nov 2, 2011 at 12:11 AM, Haseeb Avarakkan
haseeb.avarak...@gmail.com wrote:
Hello Sam;
A nice solution with Validation.
Hello Mahreen;
If you use 3, 2, 1 (number inside ) this will return the number
as text. When you do calculation excel is considering these as text. Use it
without or you can simplify the formula to;
=IFERROR(LOOKUP($E$2,{-9E+300,3;3,1;4,0;5,-3}),0)
and use it as Sam shows.
Another way is to ignore all error values when summing;
=SUMIF(A:A,9E300)
___
HTH
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
Sam Mathai Chacko
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Number Formats in French
Kent, that is where I had specifically mentioned that the numbers you write has to be equivalent to the multiple of that value with hundred. So 0.54 should be mentioned as 54 for the format to work. I did some search myself, and I couldn't come up with another format that will really do exactly what you want. Though for your sake I hope I am wrong. Sam On Wed, Nov 2, 2011 at 7:24 PM, Boucher, Kent C kent.bouc...@tbs-sct.gc.cawrote: ** ** ** ** Hi Sam, I see your point about using the other formatting conditions; it turns all my significant values after the decimal place to zero. I have gone through our correspondences and have uncovered this format you previously suggested: # ### ### ### ###\,00 ** ** I have two general issues that I can’t seem to resolve. When I use this format I lose the significance of my figures. As an example if I have $0.54 (which is in millions) and I use the above format it converts the value to ,01 which is not correct. The other part is that I can’t get a leading zero if the value is less than a whole number: ** ** English *-* $0.25 French *- 0*,25 ** ** When I change the format to equal: # ### ### ### ###\0,00 to provide a leading zero it converts whole numbers to zero and that is not correct either. Any help you can provide will be super! ** ** I really appreciate your patience with this issue. Thank you! Kent ** ** ** ** -- *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Sam Mathai Chacko *Sent:* October 31, 2011 11:12 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Number Formats in French ** ** Which is what I was point out all along in my previous mails Kent. You'll have to use the other format that I suggested. With the format you are using, it will always show a **,00 in** the end, instead of the decimal values. And you'll have to divide in by 10,000 instead of 1,000,000 just to compensate for the work around. Regards, Sam On Mon, Oct 31, 2011 at 8:36 PM, Boucher**, **Kent C kent.bouc...@tbs-sct.gc.ca wrote: The format that you provided is really good but I have one other issue. What I have been doing is taking a very large number (in the millions) and I have reducing by millions and then rounding. Here is an example: English accounting: 1) $4,333,222 - =round((4,333,222/1,000,000),2) 2) To give me = $*4.33* in Million. French Accounting: When I convert the French accounting using the provided format code; this is what I get: 1) 4 333 222 - =round((4333222/100),2) 2) It gives me = ***4,00* in** Million. Which is inconsistent with the English but the English is correct in terms of how I need to illustrate the numbers. I should have said that I was rounding down to small numbers but I didn’t think at the time it was necessary. Just to be clear: It would be IDEAL if the results for the French could be equal to and formatted like: *4,33 Million.* You guys have been very helpful – much appreciated! Thank you, Kent -- *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *B.N.Chethan Kumar *Sent:* October 28, 2011 5:52 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Number Formats in French Thanks SAM, I missed that part. please find the updated format. Regards Chethan Kumar BN On Sat, Oct 29, 2011 at 3:15 AM, Sam Mathai Chacko samde...@gmail.com wrote: The OP had requested for the decimal separator to be a comma (,) Sam On Sat, Oct 29, 2011 at 3:07 AM, B.N.Chethan Kumar chetankumar1...@gmail.com wrote: Hi Kent, I have attached the file with custom format. Kindly check if these help.** ** Regards Chethan Kumar BN On Fri, Oct 28, 2011 at 9:57 PM, HarryP Knuckles kent.bouc...@tbs-sct.gc.ca wrote: Hi guys! I have a quick question regarding formats. I have to convert english accounting procedures into French accounting procedures. In English one thousand dollars is equal to $1,000.00. In French one thousand dollars is equal to 1 000,00. the difference is that the comma before a thousand is replaced with a space AND our decimal is replaced with a comma and NO dollar sign. Another example is: In English one thousand dollars is equal to $9,555,333.00. In French one thousand dollars is equal to 9 555 333,00. Can you help me create this custom format? Your help is great appreciated!! Thank you, Kent -- FORUM RULES (925+ members already BANNED for violation) 1)
$$Excel-Macros$$ Re: Diffrent Sheets to Create from one $$$$$$$$$$$$$$$$$$$$$$$$$
Dear Friends, I have an INDI sheet which includes details of each member I want to make a different workbooks for each of them with the details of each member First 3 columns are common for each member and remaining columns from E to Q changes according to member name in cell E13,R13 respectivly There are more than 1000 emp name in my sheet . i recorded a code but it doesn’t Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+a ' Range(A1:G12).Select Selection.Copy Workbooks.Add ActiveSheet.Paste ThisFile = Range(D1).Value ActiveWorkbook.SaveAs Filename:=ThisFile Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Columns(D:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:G12).Select Selection.Copy Workbooks.Add Selection.Font.Bold = True Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Selection.Copy Windows(Book2).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal ActiveSheet.Paste ThisFile = Range(D1).Value ActiveWorkbook.SaveAs Filename:=ThisFile Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Columns(D:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:G12).Select Selection.Copy Windows(Book2).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Workbooks.Add ActiveSheet.Paste Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Range(C19).Select End Sub Plz suggest Thanks and Regards *Shashank S Bhosle* -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Re: Test Cell String for Element match and report Accuracy
First, your asking for a project. Second, your existing code is very inefficient and should have been included IN the file. example selectcase case =”abc”,”def”,”ghi”:cells(1,3).interior.colorindex=6 case else end select == You should provide after examples using cell references. Don Guillett SalesAid Software dguille...@gmail.com From: Fin Sent: Tuesday, November 01, 2011 5:59 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Test Cell String for Element match and report Accuracy Hi Sam, Asa .. thanks for the prompt replies, appreciate it. I have posted a sample file with 100 rows showing sample content. Column L (DAY3) is the target data, Column I (D3_ELE_CNT) contains the number of elements in Column L (DAY3). I have also attached a VBA script file that I generate from REXX and DB2 daily based upon the close of the market. DB2 generates many various CSV report files that I can then open the CSV in Excel, paste in the VBA code and run the macros over as necessary, saving the file as an xls workbook. I posted the REXX script example just to show how I create the VBA code I use at the moment for checking the accuracy of a single element in a cell column. It is derived from a DB2 database and created daily using current stock data to calculate the stock symbol and append a condition of POS/NEG. If I am able to get some VBA code to perform what I have described in my previous post, then I will need to write a similar REXX script to automatically create the new VBA code with current stock conditions on a daily basis to test the various DAY 3 cell strings. So, cell L2 contains the string: [AIG_VNEG]+[GE_VNEG]+[JPM_VNEG]+AXP_NEG+BAC_NEG+JNJ_NEG+T_NEG+TRV_NEG+UTX_NEG+WMT_NEG This is 10 elements as indicated in I2 (D3_ELE_CNT). Based upon the contents of the VBA attached script file, the 3 elements in [ ] brackets were correct and the remaining 7 elements were wrong, so the result would have been as follows: 7,AXP_NEG+BAC_NEG+JNJ_NEG+T_NEG+TRV_NEG+UTX_NEG+WMT_NEG,30% The attached VBA script file contains the individual elements as they closed on 10/31/2011 in the macro titled 'Sub Color_Active_Yellow()'. Presently the macro only contains VPOS/VNEG/PPOS/PNEG variants of the various stocks, but it will also contain POS/NEG variants in due course. If Columns L was individual cells it wold work fine coloring the background Yellow for correct and ACTIVE stocks. I have an ARRAY in REXX of the CLOSE conditions so can write a CASE statement that contains the correct values to test and write that out to VBA code in a text file. What I need, is to be able to read a string of elements and count how many were wrong based on this array, writing the result to Column N, then writing the actual list of wrong elements to column O (Again separated by '+'). I can then calculate the result against column I2 and write an accuracy percentage in say column P using a formula. So in essence I need some kind soul to be able to help me write the VBA code which I will then write into REXX to produce daily. Many thanks again for any assistance. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Matching business names
I want to hank all members of this group VERY much for the help! We did as suggested, but found it became a somewhat arduos task! It was more accurate to match the 300 vendor-names by eye after both lists were sorted. We divided and conquered! Job creation!! I appreciate all the help, and will be back for more support when next needed! Regards, Darryl On Nov 1, 10:27 am, Paul schreiner_p...@att.net wrote: Your first task is to standardize the lists. I would suggest first taking both lists and make a copy. then, in the copy, remove all punctuation, and common terms like inc, company, the, lastly: double-spaces. Then, I would build a dictionary of terms by loading the master list into a Dictionary Object (converting to Upper case). Next, add each separate WORD of the name to the Dictionary, saving the full name with the object. Add to the object any additional company names that have the word in common. This creates a term that has an array of name values. once the dictionary object is created from the Master list, (5-10 seconds) you then begin searching through your smaller list. If it finds an EXACT match for the name, indicate it and move on. If no exact match is found, begin looking for individual terms. By keeping track of the names that contain individual terms, you can create a list of possible matches. For instance, if you're looking for Acme Industrial Corporation You may find: Acme Industrial Widget Corporation (3) Acme Industrial Company (2) Acme Industrial Widget Division (2) Acme Corporation (2) Acme, Inc. (1) you can then sort them by relevance by the number of matching terms, or even the POSITION the terms hold. If you can send or post a sample file, I can probably throw something together in a few minutes. It would take me longer to generate the lists on my own than it would be for me to write the code! hope I can help Paul On Oct 31, 4:45 pm, Darryl d...@dgoebel.com wrote: I have a spreadsheet containing about 7K Business Names. It is considered a Master List of vendors. I have another with about 300 names, and I need to know if any of these are contained in the first one. The problem is the smaller one may list the company name as The Acme Company, while the larger (master) list may call it Acme, Inc. There does not appear to be anything certain about either entry... Can someone help me get the process started? Thanks in advance, Darryl- Hide quoted text - - Show quoted text - -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Diffrent Sheets to Create from one $$$$$$$$$$$$$$$$$$$$$$$$$
Why do you keep sending this so often. Have you not seen any replies? Don Guillett SalesAid Software dguille...@gmail.com From: shashank bhosle Sent: Wednesday, November 02, 2011 10:58 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Diffrent Sheets to Create from one $ I have an INDI sheet which includes details of each member I want to make a different workbooks for each of them with the details of each member First 3 columns are common for each member and remaining columns from E to Q changes according to member name in cell E13,R13 respectivly There are more than 1000 emp name in my sheet . i recorded a code but it doesn’t Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+a ' Range(A1:G12).Select Selection.Copy Workbooks.Add ActiveSheet.Paste ThisFile = Range(D1).Value ActiveWorkbook.SaveAs Filename:=ThisFile Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Columns(D:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:G12).Select Selection.Copy Workbooks.Add Selection.Font.Bold = True Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Selection.Copy Windows(Book2).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal ActiveSheet.Paste ThisFile = Range(D1).Value ActiveWorkbook.SaveAs Filename:=ThisFile Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Columns(D:G).Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Range(A1:G12).Select Selection.Copy Windows(Book2).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Workbooks.Add ActiveSheet.Paste Windows(adhoc Project1.xls).Activate ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlNormal Range(C19).Select End Sub Plz suggest Thanks and Regards Shashank S Bhosle -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Required help in macro / vba coding........open a file in a particular folder
Thanks.your formula is working. On Wed, Nov 2, 2011 at 4:40 AM, dguillett1 dguille...@gmail.com wrote: If you only have that many why not just do thru a formula where the file does not have to be open. Notice the placement of the ' You could even use indirect to put in the file name, etc ='C:\aa\[3.xls]Sheet24'!A3 ='C:\Users\Administrator\Desktop\05112012\[1.xls]sheet1'!a1 Don Guillett SalesAid Software dguille...@gmail.com *From:* Indrajit $nai talk2indra...@gmail.com *Sent:* Tuesday, November 01, 2011 4:36 PM *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Required help in macro / vba codingopen a file in a particular folder Hi All, I need your help regarding a macro issue. Suppose I have 5 folder in different in my computer desktop and each folder contains a excel file (same name). Now I want a macro which will open each folder and then open the file and will copy the first cell then paste it in my current file cell, like a1 then a2. Now if I change my folder path, it open that folder then the file and contuniue the above mention work. If the particular folder name; which mention in the excel file not found should give a message then again it will continue. My problem is, it's opening the particular folder, if I change the folder name or changing the path in the current excel file it is not working. Is it possible to do this job through a macro! if possible then please send me the code. Thanks in advance. -- Indrajit talk2indra...@gmail.com Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Indrajit Snai +91 9051755745 talk2indra...@gmail.com http://globalwarming-globalissue.blogspot.com/http://talk2indrajit.blogspot.com/2010/09/global-warming.html Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
Re: $$Excel-Macros$$ Required help in macro / vba coding........open a file in a particular folder
You are welcome. As I said, it would be easy to change the variables. Don Guillett SalesAid Software dguille...@gmail.com From: Indrajit $nai Sent: Wednesday, November 02, 2011 4:55 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Required help in macro / vba codingopen a file in a particular folder Thanks.your formula is working. On Wed, Nov 2, 2011 at 4:40 AM, dguillett1 dguille...@gmail.com wrote: If you only have that many why not just do thru a formula where the file does not have to be open. Notice the placement of the ' You could even use indirect to put in the file name, etc ='C:\aa\[3.xls]Sheet24'!A3 ='C:\Users\Administrator\Desktop\05112012\[1.xls]sheet1'!a1 Don Guillett SalesAid Software dguille...@gmail.com From: Indrajit $nai Sent: Tuesday, November 01, 2011 4:36 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Required help in macro / vba codingopen a file in a particular folder Hi All, I need your help regarding a macro issue. Suppose I have 5 folder in different in my computer desktop and each folder contains a excel file (same name). Now I want a macro which will open each folder and then open the file and will copy the first cell then paste it in my current file cell, like a1 then a2. Now if I change my folder path, it open that folder then the file and contuniue the above mention work. If the particular folder name; which mention in the excel file not found should give a message then again it will continue. My problem is, it's opening the particular folder, if I change the folder name or changing the path in the current excel file it is not working. Is it possible to do this job through a macro! if possible then please send me the code. Thanks in advance. -- Indrajit talk2indra...@gmail.com Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Indrajit Snai +91 9051755745 talk2indra...@gmail.com http://globalwarming-globalissue.blogspot.com/ Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post
Re: $$Excel-Macros$$ Help
Mahreen, If you have multiple ratio values define a name for them each, like Table1, Table2 choose which table to use. See the attached. HTH Haseeb -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Formula_in_Validation_List.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ Most Helpful Member Oct'11 - Sam Mathai Chacko
Congrats Sam! Lots of generous effort on your behalf. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Haseeb Avarakkan Sent: Tuesday, November 01, 2011 12:21 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Most Helpful Member Oct'11 - Sam Mathai Chacko Congratulations Sam -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Time Calculation From Two Different Formats
If you only want the finish time without splitting it in to Date and Time, you could also use =LEFT(TRIM(A23),10)+TEXT(RIGHT(TRIM(A23),4),00\:00)+(B23/(60*24)) in mm/dd/ hh:mm format Regards, Sam Mathai Chacko On Thu, Nov 3, 2011 at 5:52 AM, Haseeb Avarakkan haseeb.avarak...@gmail.com wrote: Hello John, If the Arrival Date Time is 09/08/2011 2310 Length is 343 (Row 3 data) Finish date time is 09/09/2011 04:53. Arrival Finish Date are not same. Here is the another way. See the attached. __ Haseeb -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- Sam Mathai Chacko -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Test Cell String for Element match and report Accuracy
Haseeb, thank you so much for your efforts, that is amazing I must say. I'm frankly staggered how you achieved that with formulas. Now I must admit I'm lost as to how you did so, but I kind of need to know if I am to repeat the task with the next days report. The end of day data that the original rexx script contained was taken from my stock database at the close of business. If I am to test the day3 strings with say today's data, I need to refresh it and then test that against the new active strings. The report will produce new strings to be tested each day against new close of day data. So, how do I add the latest close of day data to whatever you did, and then apply that to todays report ? I'm not sure I am explaining this correctly. Basically there will be a fresh CSV file with a varying number of rows per day (report with day3 strings) and obviously fresh close of day stock data (with a fixed number of stock symbols) to test the strings. I need a way to be able to test one against the other. I'm a bit lost here I am afraid. And as an aside, to previous post by Don, I'm sorry but this was my first attempt at Excel / VBA coding so please forgive my crude and inefficient code. I frankly had no idea what I was doing when I started and was simply pleased enough to get to work at all. I'm not sure how you define a 'project' but if that is the case, then I guess I am sorry for that too. Thanks again Haseeb, you have no idea how much I appreciate your help. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Need to copy of 1st sheet of every workbook in a single work book
Sub copy_first_sheet_from_different_workbooks_to_single() Application.DisplayAlerts = False Application.ScreenUpdating = False Dim fld As Object, fil As Object Dim ask As Workbook, ask2 As Workbook With Application.FileDialog(msoFileDialogFolderPicker) .Title = Choose the folder .InitialFileName = c:\ .Show End With fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) \ Set fso = CreateObject(scripting.filesystemobject) Set fld = fso.getfolder(fldpath) Set ask = Workbooks.Add For Each fil In fld.Files If UCase(Right(fil.Path, 4)) = UCase(.xls) Or UCase(Right(fil.Path, 5)) = UCase(.xlsx) Then Set ask2 = Workbooks.Open(fil.Path) ask2.Sheets(1).Copy After:=ask.Sheets(Sheets.Count) ask2.Close End If Next fil Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub On Wed, Nov 2, 2011 at 3:43 PM, mrinal saha mris...@gmail.com wrote: Hi Sandeep, Save this attachment in the folder where you have all the workbooks and try to run the macro (Button) on page 1 of this file. Hope this helps, Mrinal On Wed, Nov 2, 2011 at 2:08 PM, chhajersand...@gmail.com wrote: Dear all, I have many work book in a single folder. I need a macro which if I run will copy the 1st sheet of every workbook in a single workbook (though as separate sheet). Like if I have 2 workbook viz WB1 and WB2 then I want the 1st worksheet of WB1 as 1st worksheet in a new workbook (say WB3) and again 1st worksheet of WB2 as 2nd worksheet of WB3. Thanks in advance. Sandeep Chhajer. Sent on my BlackBerryŽ from Vodafone -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- *Regards* * * *Ashish Koul* *http://www.excelvbamacros.com/* P Before printing, think about the environment. -- FORUM RULES (925+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
