$$Excel-Macros$$ Re: How to Shift Chart Table Legend on Right Hand Side
Dear Genius, I agree with you that legend can be placed as a free item on any side of table. However, my question is about Legend which is permanently attached to Data Table in Chart. It remained stitched to chart data table and does not need to be adjusted manually while we increase / decrease chart width / height. We can hide Keys but not text in Data Table Legend. We cannot switch off Legend from Left Side of Data Table. My request is that I want Legend automatically attached to Data Table only on Right Side of Chart, please, if possible. Thanks On Thursday, 3 August 2017 05:33:20 UTC+3, GENIUS wrote: > > If like this, then I'm using excel 2013 > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ How to Shift Chart Table Legend on Right Hand Side
Dear Experts, Conventionally, we see Chart Data Table Legend on Left Hand Side. When Timeline is very long and there are with many items in Data Table, it becomes difficult to see Legend Text and its corresponding figure on latest date which is on far away on Right Hand Side. Is it possible to shift Data Table Legend on Right Hand Side of Chart. Sample file is attached. Thanks for your support, in advance. Regards, Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Chart Table Legend on Right Side.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ Re: Help required in excel
Check if it helps ... [image: Inline image 1] Regards, ZAFAR IQBAL +966 (14) - 325 1654, 325 3484, 325 4148 - Ext - 2011 On Sat, Jul 15, 2017 at 5:31 PM, Srinivas G wrote: > Hi Friends, > > I want to help in Excel > > ramu Ramu False > Raju Raju True > mohan Mohan False > Venu VenuTrue > SAISai False > > I first cell and second cell there will be data, please bring me a > formulae that says the True and False data based on the small letters or > capital letter. > > Regards, > Srinivas G. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Vertical Option in excel
Copy 1st row, paste special transpose at new location, copy 2nd row and paste special transpose, move on same way till last row. Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Formula for Numbers Custom Format
Dear Experts, I have little knowledge about Numbers Custom Formatting (Manually) to attach Text with Numbers. I have to use these Numbers (with Text) in Chart. In a large Data being updated frequently, manually Numbers Custom Formatting will be quite hard and chance of error are sure. Kindly provide some Formula which will attach Text to Numbers automatically while result remains as Number. Sample file is attached. Thanks a your guidance in advance. Regards, Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Formula for Numbers Custom Format.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Exact Search excluding fractions
Dear Expert, Contents in Cell F2 on Sheet is being ignored by search formula. F2 is first cell of database range "Column F". Your VB Search is working well for contents in other cells from F3 downwards. On Tuesday, 5 May 2015 14:51:37 UTC+3, Mandeep Baluja wrote: > > what F2 i didn;t get you >> > > > Regards, > Mandeep > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Exact Search excluding fractions
Dear Expert. You did a great job. I noticed that it is ignoring value in F2 (only). I think F2 is excluded from VB Range by mistake. Sorry for late response, as our Net was not operative for last 2-3 days. Regards, Zafar Iqbal On Sunday, 3 May 2015 21:38:55 UTC+3, Mandeep Baluja wrote: > > Check this Out. > > Regards, > Mandeep Baluja > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Exact Search excluding fractions
Dear Experts, I am trying to use (SEARCH and Vlookup) formula to filter out selected data from large database. Sample is attached. When I want to Search 2” ( " means Inch ), current formula brings text items containing 2” and non-related items like 1/2", 1 1/2", 2 1/2", 12”, 22” etc. When I want to Search 1/2”, current formula brings text items containing 1/2” and non-related items like 1 1/2", 2 1/2", etc. When I want to Search 4”, current formula brings text items containing 4” and non-related items like 1/4", 3/4", 1 1/4", 1 3/4", 14”, 24”, etc. When I want to Search 1/4”, current formula brings text items containing 1/4” and non-related items like 1 1/4", 2 1/4", etc. Kindly help to exclude texts with non-related fractions and attached integers on left side. Thanks Regards, ZAFAR IQBAL -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Search Test 01.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ Count Unique Value in Date Range
Dear Experts, Kindly help in formula to count unique values in give date range. I tried with sumproduct but failed. Sample data is attached. Thanks for your kind support, always. Regards, Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Count Uniques in date range.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Copy Range From Files to Sheets
Dear Expert. Excellent Work. Bundle of thanks for it. Regards, Zafar Iqbal On Saturday, 10 January 2015 10:51:39 UTC+3, Vabz wrote: > > Hi Zafar, > > check this attachment.. > > Macro will chk for sheet name exist for filename for file string > -001.xls > > If exist then data will be copied in same sheet else new sheet will be > added at the end.. > > Cheers!! > > ᐧ > > On Thu, Jan 8, 2015 at 9:22 PM, Zafar Iqbal > wrote: > >> Dear Expert, No need of replacement of existing data if it will be >> difficult to handle in big code. Thanks >> On Jan 8, 2015 6:46 PM, "Vaibhav Joshi" > >> wrote: >> >>> And what abt same records if exist.. wanna replace or keep duplicate >>> records?? >>> On Jan 8, 2015 9:15 PM, "Zafar Iqbal" > >>> wrote: >>> >>>> First run of code will create summary sheets 2400, 2401, 600, etc. >>>> Later on while updation new files data will be pasted below existing data >>>> in related summary sheets. Like 2400-08.xls will be pasted below existing >>>> last data in summary sheet 2400. >>>> On Jan 8, 2015 5:41 PM, "Vaibhav Joshi" > >>>> wrote: >>>> >>>>> what if there is sheet with name 2400, 2401 & 600 already exist? >>>>> >>>>> What about rest of file name? >>>>> ᐧ >>>>> >>>>> + >>>>> *I did not do this for you. God is here working through me for you.* >>>>> >>>>> On Thu, Jan 8, 2015 at 2:38 PM, Zafar Iqbal >>>> > wrote: >>>>> >>>>>> Dear Experts, >>>>>> >>>>>> We are getting quick help from you. We appreciate your efforts, >>>>>> always. Attached Code copies a Range from Sheet1 from Selected files & >>>>>> Paste in Active Sheet below each other. Kindly help in making this code >>>>>> more flexible as per below requirement. >>>>>> >>>>>> >>>>>> There are +400 excel files named as 2400-01.xls, 2400-02.xls, >>>>>> 2400-03.xls and so on >>>>>> >>>>>> 2401-01.xls, 2401-02.xls, 2401-03.xls and so on >>>>>> >>>>>> 600-01.xls, 600-02.xls, 600-03.xls and so. There are other groups >>>>>> with Names like 500*.xls, 700*.xls, 710*.xls, etc >>>>>> >>>>>> For files named 2400*.xls, code should create New Sheet Named as 2400 >>>>>> and paste in it Sheet1 Range from all 2400*.xls files. >>>>>> >>>>>> For files named 2401*.xls code should create New Sheet Named as 2401 >>>>>> and paste in it Sheet1 Range from all 2401*.xls files. >>>>>> >>>>>> For file named 600*.xls code should create New Sheet Named as 600 and >>>>>> paste in it Sheet1 Range from all 600*.xls files. >>>>>> >>>>>> Your guidance & quick support always make our life comfortable. >>>>>> Thanks in advance for your help, please. >>>>>> >>>>>> Regards, >>>>>> >>>>>> Zafar Iqbal >>>>>> >>>>> -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Copy Range From Files to Sheets
Dear Expert, No need of replacement of existing data if it will be difficult to handle in big code. Thanks On Jan 8, 2015 6:46 PM, "Vaibhav Joshi" wrote: > And what abt same records if exist.. wanna replace or keep duplicate > records?? > On Jan 8, 2015 9:15 PM, "Zafar Iqbal" wrote: > >> First run of code will create summary sheets 2400, 2401, 600, etc. Later >> on while updation new files data will be pasted below existing data in >> related summary sheets. Like 2400-08.xls will be pasted below existing last >> data in summary sheet 2400. >> On Jan 8, 2015 5:41 PM, "Vaibhav Joshi" wrote: >> >>> what if there is sheet with name 2400, 2401 & 600 already exist? >>> >>> What about rest of file name? >>> ᐧ >>> >>> + >>> *I did not do this for you. God is here working through me for you.* >>> >>> On Thu, Jan 8, 2015 at 2:38 PM, Zafar Iqbal wrote: >>> >>>> Dear Experts, >>>> >>>> We are getting quick help from you. We appreciate your efforts, always. >>>> Attached Code copies a Range from Sheet1 from Selected files & Paste in >>>> Active Sheet below each other. Kindly help in making this code more >>>> flexible as per below requirement. >>>> >>>> >>>> There are +400 excel files named as 2400-01.xls, 2400-02.xls, >>>> 2400-03.xls and so on >>>> >>>> 2401-01.xls, 2401-02.xls, 2401-03.xls and so on >>>> >>>> 600-01.xls, 600-02.xls, 600-03.xls and so. There are other groups with >>>> Names like 500*.xls, 700*.xls, 710*.xls, etc >>>> >>>> For files named 2400*.xls, code should create New Sheet Named as 2400 >>>> and paste in it Sheet1 Range from all 2400*.xls files. >>>> >>>> For files named 2401*.xls code should create New Sheet Named as 2401 >>>> and paste in it Sheet1 Range from all 2401*.xls files. >>>> >>>> For file named 600*.xls code should create New Sheet Named as 600 and >>>> paste in it Sheet1 Range from all 600*.xls files. >>>> >>>> Your guidance & quick support always make our life comfortable. Thanks >>>> in advance for your help, please. >>>> >>>> Regards, >>>> >>>> Zafar Iqbal >>>> >>>> -- >>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>> https://www.facebook.com/discussexcel >>>> >>>> FORUM RULES >>>> >>>> 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) Jobs posting is not allowed. >>>> 6) Sharing copyrighted material and their links is not allowed. >>>> >>>> NOTE : Don't ever post confidential data in a workbook. Forum owners >>>> and members are not responsible for any loss. >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "MS EXCEL AND VBA MACROS" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to excel-macros+unsubscr...@googlegroups.com. >>>> To post to this group, send email to excel-macros@googlegroups.com. >>>> Visit this group at http://groups.google.com/group/excel-macros. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> -- >>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>> https://www.facebook.com/discussexcel >>> >>> FORUM RULES >>> >>> 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 bypa
Re: $$Excel-Macros$$ Copy Range From Files to Sheets
Dear expert, Summary sheets for other files like 710, 720, 725, 525, 3490, etc will be created when later on code will run and it will find such files. Thanks for your quick help. On Jan 8, 2015 5:41 PM, "Vaibhav Joshi" wrote: > what if there is sheet with name 2400, 2401 & 600 already exist? > > What about rest of file name? > ᐧ > > + > *I did not do this for you. God is here working through me for you.* > > On Thu, Jan 8, 2015 at 2:38 PM, Zafar Iqbal wrote: > >> Dear Experts, >> >> We are getting quick help from you. We appreciate your efforts, always. >> Attached Code copies a Range from Sheet1 from Selected files & Paste in >> Active Sheet below each other. Kindly help in making this code more >> flexible as per below requirement. >> >> >> There are +400 excel files named as 2400-01.xls, 2400-02.xls, 2400-03.xls >> and so on >> >> 2401-01.xls, 2401-02.xls, 2401-03.xls and so on >> >> 600-01.xls, 600-02.xls, 600-03.xls and so. There are other groups with >> Names like 500*.xls, 700*.xls, 710*.xls, etc >> >> For files named 2400*.xls, code should create New Sheet Named as 2400 and >> paste in it Sheet1 Range from all 2400*.xls files. >> >> For files named 2401*.xls code should create New Sheet Named as 2401 and >> paste in it Sheet1 Range from all 2401*.xls files. >> >> For file named 600*.xls code should create New Sheet Named as 600 and >> paste in it Sheet1 Range from all 600*.xls files. >> >> Your guidance & quick support always make our life comfortable. Thanks in >> advance for your help, please. >> >> Regards, >> >> Zafar Iqbal >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 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) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >> To post to this group, send email to excel-macros@googlegroups.com. >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/excel-macros/ZPWVGa6U-LE/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook
Re: $$Excel-Macros$$ Copy Range From Files to Sheets
First run of code will create summary sheets 2400, 2401, 600, etc. Later on while updation new files data will be pasted below existing data in related summary sheets. Like 2400-08.xls will be pasted below existing last data in summary sheet 2400. On Jan 8, 2015 5:41 PM, "Vaibhav Joshi" wrote: > what if there is sheet with name 2400, 2401 & 600 already exist? > > What about rest of file name? > ᐧ > > + > *I did not do this for you. God is here working through me for you.* > > On Thu, Jan 8, 2015 at 2:38 PM, Zafar Iqbal wrote: > >> Dear Experts, >> >> We are getting quick help from you. We appreciate your efforts, always. >> Attached Code copies a Range from Sheet1 from Selected files & Paste in >> Active Sheet below each other. Kindly help in making this code more >> flexible as per below requirement. >> >> >> There are +400 excel files named as 2400-01.xls, 2400-02.xls, 2400-03.xls >> and so on >> >> 2401-01.xls, 2401-02.xls, 2401-03.xls and so on >> >> 600-01.xls, 600-02.xls, 600-03.xls and so. There are other groups with >> Names like 500*.xls, 700*.xls, 710*.xls, etc >> >> For files named 2400*.xls, code should create New Sheet Named as 2400 and >> paste in it Sheet1 Range from all 2400*.xls files. >> >> For files named 2401*.xls code should create New Sheet Named as 2401 and >> paste in it Sheet1 Range from all 2401*.xls files. >> >> For file named 600*.xls code should create New Sheet Named as 600 and >> paste in it Sheet1 Range from all 600*.xls files. >> >> Your guidance & quick support always make our life comfortable. Thanks in >> advance for your help, please. >> >> Regards, >> >> Zafar Iqbal >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 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) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >> To post to this group, send email to excel-macros@googlegroups.com. >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 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) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/excel-macros/ZPWVGa6U-LE/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) !
$$Excel-Macros$$ Copy Range From Files to Sheets
Dear Experts, We are getting quick help from you. We appreciate your efforts, always. Attached Code copies a Range from Sheet1 from Selected files & Paste in Active Sheet below each other. Kindly help in making this code more flexible as per below requirement. There are +400 excel files named as 2400-01.xls, 2400-02.xls, 2400-03.xls and so on 2401-01.xls, 2401-02.xls, 2401-03.xls and so on 600-01.xls, 600-02.xls, 600-03.xls and so. There are other groups with Names like 500*.xls, 700*.xls, 710*.xls, etc For files named 2400*.xls, code should create New Sheet Named as 2400 and paste in it Sheet1 Range from all 2400*.xls files. For files named 2401*.xls code should create New Sheet Named as 2401 and paste in it Sheet1 Range from all 2401*.xls files. For file named 600*.xls code should create New Sheet Named as 600 and paste in it Sheet1 Range from all 600*.xls files. Your guidance & quick support always make our life comfortable. Thanks in advance for your help, please. Regards, Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Copy Range from Files to Sheets.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert, Great Job done by you. You did an excellent work. So nice of you & surely I expect more help fro you for next challenges. Thanks Regards, Zafar Iqbal --- On Tue, Jan 6, 2015 at 6:35 PM, Vaibhav Joshi wrote: > Also check over write module > On Jan 6, 2015 9:00 PM, "Zafar Iqbal" wrote: > >> Dear Expert, Thanks for it. I will shift overwritten data manually from >> end to its proper location. You did great work for us. Bundle of thanks. >> >> Regards, >> ZAFAR IQBAL >> --- >> >> On Tue, Jan 6, 2015 at 6:15 PM, Vaibhav Joshi wrote: >> >>> Check this..now i rectified module for Replace & add to end.. >>> >>> Cheers!! >>> >>> + >>> *I did not do this for you. God is here working through me for you.* >>> >>> On Tue, Jan 6, 2015 at 8:40 PM, Zafar Iqbal wrote: >>> >>>> Typing mistake. Sort 600-01, 600-02, 600-03 and so. >>>> >>>> --- >>>> >>>> On Tue, Jan 6, 2015 at 6:08 PM, Zafar Iqbal wrote: >>>> >>>>> Dear Expert, Thanks for it. Can we sort data later on in code and >>>>> position them in proper ascending 6001-01, 600-02 & so on? >>>>> >>>>> Regards, >>>>> ZAFAR IQBAL >>>>> --- >>>>> >>>>> On Tue, Jan 6, 2015 at 5:59 PM, Vaibhav Joshi wrote: >>>>> >>>>>> Try code from module ..Data_Flex_Merger_AddToEnd >>>>>> >>>>>> This wll delete existing data & add that file at the end.. >>>>>> >>>>>> + >>>>>> *I did not do this for you. God is here working through me for you.* >>>>>> >>>>>> On Tue, Jan 6, 2015 at 8:17 PM, Vaibhav Joshi wrote: >>>>>> >>>>>>> can you send me three separate files.. >>>>>>> >>>>>>> + >>>>>>> *I did not do this for you. God is here working through me for you.* >>>>>>> >>>>>>> On Tue, Jan 6, 2015 at 8:11 PM, Zafar Iqbal >>>>>>> wrote: >>>>>>> >>>>>>>> Dear Expert, I appreciate your guidance. There is minor correction >>>>>>>> still needed. Now, it is deleting some data down the bottom. >>>>>>>> >>>>>>>> Data of Files 601-01, 600-02, 600-03, 600-04 & 600-05 Exists in >>>>>>>> sheet. Overwrite of first file 600-01 (only), Delete Contents of >>>>>>>> 600-02 and >>>>>>>> 600-03 (two files) whereas its border / format exists and below data of >>>>>>>> 600-04/05 in OK un-touch. When we overwrite second file 600-02, it >>>>>>>> delete >>>>>>>> contents of below file 600-03 (one file only) and 600-04/05 is OK. >>>>>>>> When we >>>>>>>> overwrite 600-03, it deletes contents of 600-04 and 600-05 in OK >>>>>>>> un-touch. >>>>>>>> When overwrite of 600-04 is carried out there is no error to below data >>>>>>>> (un-touch). Please help for this error. Thanks >>>>>>>> >>>>>>>> Regards, >>>>>>>> Zafar Iqbal >>>>>>>> >>>>>>>> On Tuesday, 6 January 2015 17:17:58 UTC+3, Vabz wrote: >>>>>>>>> >>>>>>>>> Check this >>>>>>>>> >>>>>>>>> In your original code you was selecting whole range from data >>>>>>>>> file... >>>>>>>>> >>>>>>>>> I rectified to select upto last used row... >>>>>>>>> >>>>>>>>> Cheers!! >>>>>>>>> >>>>>>>>> + >>>>>>>>> *I did not do this for you. God is here working through me for >>>>>>>>> you.* >>>>>>>>> >>>>>>>> -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert, Thanks for it. I will shift overwritten data manually from end to its proper location. You did great work for us. Bundle of thanks. Regards, ZAFAR IQBAL --- On Tue, Jan 6, 2015 at 6:15 PM, Vaibhav Joshi wrote: > Check this..now i rectified module for Replace & add to end.. > > Cheers!! > > + > *I did not do this for you. God is here working through me for you.* > > On Tue, Jan 6, 2015 at 8:40 PM, Zafar Iqbal wrote: > >> Typing mistake. Sort 600-01, 600-02, 600-03 and so. >> >> --- >> >> On Tue, Jan 6, 2015 at 6:08 PM, Zafar Iqbal wrote: >> >>> Dear Expert, Thanks for it. Can we sort data later on in code and >>> position them in proper ascending 6001-01, 600-02 & so on? >>> >>> Regards, >>> ZAFAR IQBAL >>> --- >>> >>> On Tue, Jan 6, 2015 at 5:59 PM, Vaibhav Joshi wrote: >>> >>>> Try code from module ..Data_Flex_Merger_AddToEnd >>>> >>>> This wll delete existing data & add that file at the end.. >>>> >>>> + >>>> *I did not do this for you. God is here working through me for you.* >>>> >>>> On Tue, Jan 6, 2015 at 8:17 PM, Vaibhav Joshi wrote: >>>> >>>>> can you send me three separate files.. >>>>> >>>>> + >>>>> *I did not do this for you. God is here working through me for you.* >>>>> >>>>> On Tue, Jan 6, 2015 at 8:11 PM, Zafar Iqbal >>>>> wrote: >>>>> >>>>>> Dear Expert, I appreciate your guidance. There is minor correction >>>>>> still needed. Now, it is deleting some data down the bottom. >>>>>> >>>>>> Data of Files 601-01, 600-02, 600-03, 600-04 & 600-05 Exists in >>>>>> sheet. Overwrite of first file 600-01 (only), Delete Contents of 600-02 >>>>>> and >>>>>> 600-03 (two files) whereas its border / format exists and below data of >>>>>> 600-04/05 in OK un-touch. When we overwrite second file 600-02, it delete >>>>>> contents of below file 600-03 (one file only) and 600-04/05 is OK. When >>>>>> we >>>>>> overwrite 600-03, it deletes contents of 600-04 and 600-05 in OK >>>>>> un-touch. >>>>>> When overwrite of 600-04 is carried out there is no error to below data >>>>>> (un-touch). Please help for this error. Thanks >>>>>> >>>>>> Regards, >>>>>> Zafar Iqbal >>>>>> >>>>>> On Tuesday, 6 January 2015 17:17:58 UTC+3, Vabz wrote: >>>>>>> >>>>>>> Check this >>>>>>> >>>>>>> In your original code you was selecting whole range from data file... >>>>>>> >>>>>>> I rectified to select upto last used row... >>>>>>> >>>>>>> Cheers!! >>>>>>> >>>>>>> + >>>>>>> *I did not do this for you. God is here working through me for you.* >>>>>>> >>>>>>> On Tue, Jan 6, 2015 at 7:29 PM, Zafar Iqbal >>>>>>> wrote: >>>>>>> >>>>>>>> Dear Expert, It is very nice. Thanks for it. >>>>>>>> >>>>>>>> A little problem is observed. If data over write is falling at >>>>>>>> start or in middle of existing data, it overwrite well, but delete >>>>>>>> exiting >>>>>>>> data down the bottom. >>>>>>>> >>>>>>>> For example, Data from files 601-01, 600-02 & 600-03 already >>>>>>>> exists. When overwrite of 601-01 (first file) is needed, it will >>>>>>>> delete all >>>>>>>> data below from files 600-02 and 600-03. Similarly if over write is for >>>>>>>> 600-02 only (middle file) then date below it 600-03 is deleted. Can it >>>>>>>> be >>>>>>>> rectified, please? Thanks for your efforts & excellent work, always. >>>>>>>> >>>>>>>> Regards, >>>>>>>> Zafar Iqbal >>>>>>>>
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Typing mistake. Sort 600-01, 600-02, 600-03 and so. --- On Tue, Jan 6, 2015 at 6:08 PM, Zafar Iqbal wrote: > Dear Expert, Thanks for it. Can we sort data later on in code and position > them in proper ascending 6001-01, 600-02 & so on? > > Regards, > ZAFAR IQBAL > --- > > On Tue, Jan 6, 2015 at 5:59 PM, Vaibhav Joshi wrote: > >> Try code from module ..Data_Flex_Merger_AddToEnd >> >> This wll delete existing data & add that file at the end.. >> >> + >> *I did not do this for you. God is here working through me for you.* >> >> On Tue, Jan 6, 2015 at 8:17 PM, Vaibhav Joshi wrote: >> >>> can you send me three separate files.. >>> >>> +++++ >>> *I did not do this for you. God is here working through me for you.* >>> >>> On Tue, Jan 6, 2015 at 8:11 PM, Zafar Iqbal wrote: >>> >>>> Dear Expert, I appreciate your guidance. There is minor correction >>>> still needed. Now, it is deleting some data down the bottom. >>>> >>>> Data of Files 601-01, 600-02, 600-03, 600-04 & 600-05 Exists in sheet. >>>> Overwrite of first file 600-01 (only), Delete Contents of 600-02 and 600-03 >>>> (two files) whereas its border / format exists and below data of 600-04/05 >>>> in OK un-touch. When we overwrite second file 600-02, it delete contents of >>>> below file 600-03 (one file only) and 600-04/05 is OK. When we overwrite >>>> 600-03, it deletes contents of 600-04 and 600-05 in OK un-touch. When >>>> overwrite of 600-04 is carried out there is no error to below data >>>> (un-touch). Please help for this error. Thanks >>>> >>>> Regards, >>>> Zafar Iqbal >>>> >>>> On Tuesday, 6 January 2015 17:17:58 UTC+3, Vabz wrote: >>>>> >>>>> Check this >>>>> >>>>> In your original code you was selecting whole range from data file... >>>>> >>>>> I rectified to select upto last used row... >>>>> >>>>> Cheers!! >>>>> >>>>> + >>>>> *I did not do this for you. God is here working through me for you.* >>>>> >>>>> On Tue, Jan 6, 2015 at 7:29 PM, Zafar Iqbal wrote: >>>>> >>>>>> Dear Expert, It is very nice. Thanks for it. >>>>>> >>>>>> A little problem is observed. If data over write is falling at start >>>>>> or in middle of existing data, it overwrite well, but delete exiting data >>>>>> down the bottom. >>>>>> >>>>>> For example, Data from files 601-01, 600-02 & 600-03 already exists. >>>>>> When overwrite of 601-01 (first file) is needed, it will delete all data >>>>>> below from files 600-02 and 600-03. Similarly if over write is for 600-02 >>>>>> only (middle file) then date below it 600-03 is deleted. Can it be >>>>>> rectified, please? Thanks for your efforts & excellent work, always. >>>>>> >>>>>> Regards, >>>>>> Zafar Iqbal >>>>>> >>>>>> On Tuesday, 6 January 2015 16:37:10 UTC+3, Vabz wrote: >>>>>>> >>>>>>> Hi >>>>>>> >>>>>>> Check module 2 *Data_Flex_Merger_Overwrite* >>>>>>> >>>>>>> This will meet your requirement..i am not sure of your file >>>>>>> structure but i have tried my best guessing... >>>>>>> >>>>>>> Cheers!! >>>>>>> >>>>>>> + >>>>>>> *I did not do this for you. God is here working through me for you.* >>>>>>> >>>>>>> On Tue, Jan 6, 2015 at 12:15 PM, Zafar Iqbal >>>>>>> wrote: >>>>>>> >>>>>>>> Dear Expert, >>>>>>>> >>>>>>>> Now, It is working fine. Bundle of Thanks for it. >>>>>>>> >>>>>>>> I checked my data files and found that exiting files will be >>>>>>>> revised in few cases. In this scenario, only contents of existing file >>>>>>>> will >>>>>>>> modify and number of rows will remain same, i.e data height which you >&
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert, Thanks for it. Can we sort data later on in code and position them in proper ascending 6001-01, 600-02 & so on? Regards, ZAFAR IQBAL --- On Tue, Jan 6, 2015 at 5:59 PM, Vaibhav Joshi wrote: > Try code from module ..Data_Flex_Merger_AddToEnd > > This wll delete existing data & add that file at the end.. > > + > *I did not do this for you. God is here working through me for you.* > > On Tue, Jan 6, 2015 at 8:17 PM, Vaibhav Joshi wrote: > >> can you send me three separate files.. >> >> + >> *I did not do this for you. God is here working through me for you.* >> >> On Tue, Jan 6, 2015 at 8:11 PM, Zafar Iqbal wrote: >> >>> Dear Expert, I appreciate your guidance. There is minor correction still >>> needed. Now, it is deleting some data down the bottom. >>> >>> Data of Files 601-01, 600-02, 600-03, 600-04 & 600-05 Exists in sheet. >>> Overwrite of first file 600-01 (only), Delete Contents of 600-02 and 600-03 >>> (two files) whereas its border / format exists and below data of 600-04/05 >>> in OK un-touch. When we overwrite second file 600-02, it delete contents of >>> below file 600-03 (one file only) and 600-04/05 is OK. When we overwrite >>> 600-03, it deletes contents of 600-04 and 600-05 in OK un-touch. When >>> overwrite of 600-04 is carried out there is no error to below data >>> (un-touch). Please help for this error. Thanks >>> >>> Regards, >>> Zafar Iqbal >>> >>> On Tuesday, 6 January 2015 17:17:58 UTC+3, Vabz wrote: >>>> >>>> Check this >>>> >>>> In your original code you was selecting whole range from data file... >>>> >>>> I rectified to select upto last used row... >>>> >>>> Cheers!! >>>> >>>> + >>>> *I did not do this for you. God is here working through me for you.* >>>> >>>> On Tue, Jan 6, 2015 at 7:29 PM, Zafar Iqbal wrote: >>>> >>>>> Dear Expert, It is very nice. Thanks for it. >>>>> >>>>> A little problem is observed. If data over write is falling at start >>>>> or in middle of existing data, it overwrite well, but delete exiting data >>>>> down the bottom. >>>>> >>>>> For example, Data from files 601-01, 600-02 & 600-03 already exists. >>>>> When overwrite of 601-01 (first file) is needed, it will delete all data >>>>> below from files 600-02 and 600-03. Similarly if over write is for 600-02 >>>>> only (middle file) then date below it 600-03 is deleted. Can it be >>>>> rectified, please? Thanks for your efforts & excellent work, always. >>>>> >>>>> Regards, >>>>> Zafar Iqbal >>>>> >>>>> On Tuesday, 6 January 2015 16:37:10 UTC+3, Vabz wrote: >>>>>> >>>>>> Hi >>>>>> >>>>>> Check module 2 *Data_Flex_Merger_Overwrite* >>>>>> >>>>>> This will meet your requirement..i am not sure of your file structure >>>>>> but i have tried my best guessing... >>>>>> >>>>>> Cheers!! >>>>>> >>>>>> + >>>>>> *I did not do this for you. God is here working through me for you.* >>>>>> >>>>>> On Tue, Jan 6, 2015 at 12:15 PM, Zafar Iqbal >>>>>> wrote: >>>>>> >>>>>>> Dear Expert, >>>>>>> >>>>>>> Now, It is working fine. Bundle of Thanks for it. >>>>>>> >>>>>>> I checked my data files and found that exiting files will be revised >>>>>>> in few cases. In this scenario, only contents of existing file will >>>>>>> modify >>>>>>> and number of rows will remain same, i.e data height which you pointed >>>>>>> out >>>>>>> earlier will not change. >>>>>>> So, I request that if code finds that file name already exists in >>>>>>> sheet, then send message "do you want to replace data - File Name?", >>>>>>> upon >>>>>>> yes, replace else go to next file. It will be great help for me, please. >>>>>>> Thanks in advance. >>>>>>> >>>>>>> Regards,
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert, I appreciate your guidance. There is minor correction still needed. Now, it is deleting some data down the bottom. Data of Files 601-01, 600-02, 600-03, 600-04 & 600-05 Exists in sheet. Overwrite of first file 600-01 (only), Delete Contents of 600-02 and 600-03 (two files) whereas its border / format exists and below data of 600-04/05 in OK un-touch. When we overwrite second file 600-02, it delete contents of below file 600-03 (one file only) and 600-04/05 is OK. When we overwrite 600-03, it deletes contents of 600-04 and 600-05 in OK un-touch. When overwrite of 600-04 is carried out there is no error to below data (un-touch). Please help for this error. Thanks Regards, Zafar Iqbal On Tuesday, 6 January 2015 17:17:58 UTC+3, Vabz wrote: > > Check this > > In your original code you was selecting whole range from data file... > > I rectified to select upto last used row... > > Cheers!! > > + > *I did not do this for you. God is here working through me for you.* > > On Tue, Jan 6, 2015 at 7:29 PM, Zafar Iqbal > wrote: > >> Dear Expert, It is very nice. Thanks for it. >> >> A little problem is observed. If data over write is falling at start or >> in middle of existing data, it overwrite well, but delete exiting data down >> the bottom. >> >> For example, Data from files 601-01, 600-02 & 600-03 already exists. When >> overwrite of 601-01 (first file) is needed, it will delete all data below >> from files 600-02 and 600-03. Similarly if over write is for 600-02 only >> (middle file) then date below it 600-03 is deleted. Can it be rectified, >> please? Thanks for your efforts & excellent work, always. >> >> Regards, >> Zafar Iqbal >> >> On Tuesday, 6 January 2015 16:37:10 UTC+3, Vabz wrote: >>> >>> Hi >>> >>> Check module 2 *Data_Flex_Merger_Overwrite* >>> >>> This will meet your requirement..i am not sure of your file structure >>> but i have tried my best guessing... >>> >>> Cheers!! >>> >>> + >>> *I did not do this for you. God is here working through me for you.* >>> >>> On Tue, Jan 6, 2015 at 12:15 PM, Zafar Iqbal wrote: >>> >>>> Dear Expert, >>>> >>>> Now, It is working fine. Bundle of Thanks for it. >>>> >>>> I checked my data files and found that exiting files will be revised in >>>> few cases. In this scenario, only contents of existing file will modify >>>> and >>>> number of rows will remain same, i.e data height which you pointed out >>>> earlier will not change. >>>> So, I request that if code finds that file name already exists in >>>> sheet, then send message "do you want to replace data - File Name?", upon >>>> yes, replace else go to next file. It will be great help for me, please. >>>> Thanks in advance. >>>> >>>> Regards, >>>> Zafar Iqba >>>> >>>> On Monday, 5 January 2015 17:59:56 UTC+3, Vabz wrote: >>>>> >>>>> ok, check this now.. >>>>> >>>>> Cheers!! >>>>> >>>>> + >>>>> *I did not do this for you. God is here working through me for you.* >>>>> >>>>> On Mon, Jan 5, 2015 at 8:22 PM, Zafar Iqbal wrote: >>>>> >>>>>> Dear Expert, >>>>>> >>>>>> Code is stuck at line For j = LBound(nCount) To UBound(nCount) when >>>>>> run first time while Active Sheet was blank and data is pasted from >>>>>> target >>>>>> files.On Second run it is working as per requirement, excluding files >>>>>> whose >>>>>> Names are already present. >>>>>> >>>>>> Paste Special Column Width is not working, right now. Kindly rectify >>>>>> both problems. Thanks >>>>>> >>>>>> Regards, >>>>>> Zafar Iqbal >>>>>> >>>>>> On Monday, 5 January 2015 17:40:42 UTC+3, Vabz wrote: >>>>>>> >>>>>>> Hi >>>>>>> >>>>>>> Try code in attached excel file...I assumed shipment # will be in >>>>>>> third row of Product delivery note & there will be only one PD in excel >>>>>>> file... >>>>>>> >>>>>>> Cheers!! >>>>>>> >>>>>>> + &g
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert, It is very nice. Thanks for it.
A little problem is observed. If data over write is falling at start or in
middle of existing data, it overwrite well, but delete exiting data down
the bottom.
For example, Data from files 601-01, 600-02 & 600-03 already exists. When
overwrite of 601-01 (first file) is needed, it will delete all data below
from files 600-02 and 600-03. Similarly if over write is for 600-02 only
(middle file) then date below it 600-03 is deleted. Can it be rectified,
please? Thanks for your efforts & excellent work, always.
Regards,
Zafar Iqbal
On Tuesday, 6 January 2015 16:37:10 UTC+3, Vabz wrote:
>
> Hi
>
> Check module 2 *Data_Flex_Merger_Overwrite*
>
> This will meet your requirement..i am not sure of your file structure but
> i have tried my best guessing...
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Tue, Jan 6, 2015 at 12:15 PM, Zafar Iqbal > wrote:
>
>> Dear Expert,
>>
>> Now, It is working fine. Bundle of Thanks for it.
>>
>> I checked my data files and found that exiting files will be revised in
>> few cases. In this scenario, only contents of existing file will modify and
>> number of rows will remain same, i.e data height which you pointed out
>> earlier will not change.
>> So, I request that if code finds that file name already exists in sheet,
>> then send message "do you want to replace data - File Name?", upon yes,
>> replace else go to next file. It will be great help for me, please. Thanks
>> in advance.
>>
>> Regards,
>> Zafar Iqba
>>
>> On Monday, 5 January 2015 17:59:56 UTC+3, Vabz wrote:
>>>
>>> ok, check this now..
>>>
>>> Cheers!!
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>> On Mon, Jan 5, 2015 at 8:22 PM, Zafar Iqbal wrote:
>>>
>>>> Dear Expert,
>>>>
>>>> Code is stuck at line For j = LBound(nCount) To UBound(nCount) when
>>>> run first time while Active Sheet was blank and data is pasted from target
>>>> files.On Second run it is working as per requirement, excluding files
>>>> whose
>>>> Names are already present.
>>>>
>>>> Paste Special Column Width is not working, right now. Kindly rectify
>>>> both problems. Thanks
>>>>
>>>> Regards,
>>>> Zafar Iqbal
>>>>
>>>> On Monday, 5 January 2015 17:40:42 UTC+3, Vabz wrote:
>>>>>
>>>>> Hi
>>>>>
>>>>> Try code in attached excel file...I assumed shipment # will be in
>>>>> third row of Product delivery note & there will be only one PD in excel
>>>>> file...
>>>>>
>>>>> Cheers!!
>>>>>
>>>>> +
>>>>> *I did not do this for you. God is here working through me for you.*
>>>>>
>>>>> On Mon, Jan 5, 2015 at 6:03 PM, Zafar Iqbal wrote:
>>>>>
>>>>>> Dear Vabz,
>>>>>>
>>>>>> For this condition, I request code to exclude/skip those files whose
>>>>>> names are already present in sheet. Thanks
>>>>>>
>>>>>> Regards,
>>>>>> Zafar Iqbal
>>>>>>
>>>>>> On Monday, 5 January 2015 15:19:57 UTC+3, Vabz wrote:
>>>>>>>
>>>>>>> hi
>>>>>>>
>>>>>>> Height of every file is not same, so how you want to paste data on
>>>>>>> existing data if file no. is matching?
>>>>>>>
>>>>>>> +
>>>>>>> *I did not do this for you. God is here working through me for you.*
>>>>>>>
>>>>>>> On Mon, Jan 5, 2015 at 5:03 PM, Zafar Iqbal
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Dear Experts,
>>>>>>>>
>>>>>>>> By hit & trial method, I got file name with file path by adding
>>>>>>>> line Range("A65536").End(xlUp).Offset(3, 17) = FileName in the
>>>>>>>> code.
>>>>>>>> Please guide me how to get file name only from full path address. I
>>>>>>>> will need answer to balance points # 2, 3 & 4 from you, please.
>>>>>>>>
>>>>>>>>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert,
Now, It is working fine. Bundle of Thanks for it.
I checked my data files and found that exiting files will be revised in few
cases. In this scenario, only contents of existing file will modify and
number of rows will remain same, i.e data height which you pointed out
earlier will not change.
So, I request that if code finds that file name already exists in sheet,
then send message "do you want to replace data - File Name?", upon yes,
replace else go to next file. It will be great help for me, please. Thanks
in advance.
Regards,
Zafar Iqba
On Monday, 5 January 2015 17:59:56 UTC+3, Vabz wrote:
>
> ok, check this now..
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Mon, Jan 5, 2015 at 8:22 PM, Zafar Iqbal > wrote:
>
>> Dear Expert,
>>
>> Code is stuck at line For j = LBound(nCount) To UBound(nCount) when run
>> first time while Active Sheet was blank and data is pasted from target
>> files.On Second run it is working as per requirement, excluding files whose
>> Names are already present.
>>
>> Paste Special Column Width is not working, right now. Kindly rectify both
>> problems. Thanks
>>
>> Regards,
>> Zafar Iqbal
>>
>> On Monday, 5 January 2015 17:40:42 UTC+3, Vabz wrote:
>>>
>>> Hi
>>>
>>> Try code in attached excel file...I assumed shipment # will be in third
>>> row of Product delivery note & there will be only one PD in excel file...
>>>
>>> Cheers!!
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>> On Mon, Jan 5, 2015 at 6:03 PM, Zafar Iqbal wrote:
>>>
>>>> Dear Vabz,
>>>>
>>>> For this condition, I request code to exclude/skip those files whose
>>>> names are already present in sheet. Thanks
>>>>
>>>> Regards,
>>>> Zafar Iqbal
>>>>
>>>> On Monday, 5 January 2015 15:19:57 UTC+3, Vabz wrote:
>>>>>
>>>>> hi
>>>>>
>>>>> Height of every file is not same, so how you want to paste data on
>>>>> existing data if file no. is matching?
>>>>>
>>>>> +
>>>>> *I did not do this for you. God is here working through me for you.*
>>>>>
>>>>> On Mon, Jan 5, 2015 at 5:03 PM, Zafar Iqbal wrote:
>>>>>
>>>>>> Dear Experts,
>>>>>>
>>>>>> By hit & trial method, I got file name with file path by adding line
>>>>>> Range("A65536").End(xlUp).Offset(3, 17) = FileName in the code.
>>>>>> Please guide me how to get file name only from full path address. I
>>>>>> will need answer to balance points # 2, 3 & 4 from you, please.
>>>>>>
>>>>>>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Expert,
Code is stuck at line For j = LBound(nCount) To UBound(nCount) when run
first time while Active Sheet was blank and data is pasted from target
files.On Second run it is working as per requirement, excluding files whose
Names are already present.
Paste Special Column Width is not working, right now. Kindly rectify both
problems. Thanks
Regards,
Zafar Iqbal
On Monday, 5 January 2015 17:40:42 UTC+3, Vabz wrote:
>
> Hi
>
> Try code in attached excel file...I assumed shipment # will be in third
> row of Product delivery note & there will be only one PD in excel file...
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Mon, Jan 5, 2015 at 6:03 PM, Zafar Iqbal > wrote:
>
>> Dear Vabz,
>>
>> For this condition, I request code to exclude/skip those files whose
>> names are already present in sheet. Thanks
>>
>> Regards,
>> Zafar Iqbal
>>
>> On Monday, 5 January 2015 15:19:57 UTC+3, Vabz wrote:
>>>
>>> hi
>>>
>>> Height of every file is not same, so how you want to paste data on
>>> existing data if file no. is matching?
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>> On Mon, Jan 5, 2015 at 5:03 PM, Zafar Iqbal wrote:
>>>
>>>> Dear Experts,
>>>>
>>>> By hit & trial method, I got file name with file path by adding line
>>>> Range("A65536").End(xlUp).Offset(3, 17) = FileName in the code.
>>>> Please guide me how to get file name only from full path address. I
>>>> will need answer to balance points # 2, 3 & 4 from you, please.
>>>>
>>>>
>>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 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) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros...@googlegroups.com .
>> To post to this group, send email to excel-...@googlegroups.com
>> .
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Vabz,
For this condition, I request code to exclude/skip those files whose names
are already present in sheet. Thanks
Regards,
Zafar Iqbal
On Monday, 5 January 2015 15:19:57 UTC+3, Vabz wrote:
>
> hi
>
> Height of every file is not same, so how you want to paste data on
> existing data if file no. is matching?
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Mon, Jan 5, 2015 at 5:03 PM, Zafar Iqbal > wrote:
>
>> Dear Experts,
>>
>> By hit & trial method, I got file name with file path by adding line
>> Range("A65536").End(xlUp).Offset(3,
>> 17) = FileName in the code.
>> Please guide me how to get file name only from full path address. I will
>> need answer to balance points # 2, 3 & 4 from you, please.
>>
>>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Data Consolidate with conditions
Dear Experts,
By hit & trial method, I got file name with file path by adding line
Range("A65536").End(xlUp).Offset(3,
17) = FileName in the code.
Please guide me how to get file name only from full path address. I will
need answer to balance points # 2, 3 & 4 from you, please.
Sub Data_Flex_Merger()
Dim bookList As Workbook
Dim FileName As Variant
Dim n As Long
Dim disWB As Workbook
Set disWB = ActiveWorkbook
FileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
*.xl*", MultiSelect:=True)
Application.ScreenUpdating = False
For n = LBound(FileName) To UBound(FileName)
Set bookList = Workbooks.Open(FileName(n))
Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
disWB.Worksheets(1).Activate
Range("A65536").End(xlUp).Offset(3, 17) = FileName
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close False
Next n
Application.ScreenUpdating = True
End Sub
On Monday, 5 January 2015 12:26:39 UTC+3, Zafar Iqbal wrote:
>
> Dear Experts,
>
> VB Code in attached sample file is copying Range Sheet1(A2:Q50) from
> Selected Files (600-01.xls, 600-02.xls, 600-03.xls, etc) in folder to
> Active Sheet.
>
> Some improvement is needed in this code:-
>
> 1) Every File name should be written at first row in Column R of copied
> range.
>
> Later on New files will be added in folder and their data is needed to be
> pasted below current data in Active Sheet.
>
> 2) For data up-dation, code should copy data from only those files whose
> name is not present in Active Sheet in Column R.
>
> 3) If file name exists then send warning message, “Do you want to replace
> existing data of file (Name)?” If yes then code should replace existing
> data of this file else go to next file.
>
> 4) Paste Special Column width is needed once only when data of last file
> is pasted in active sheet. Avoid its repeating on each file.
>
> Kindly help in this matter. Thanks in advance.
>
> Regards,
>
> Zafar Iqbal
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Data Consolidate with conditions
Dear Experts, VB Code in attached sample file is copying Range Sheet1(A2:Q50) from Selected Files (600-01.xls, 600-02.xls, 600-03.xls, etc) in folder to Active Sheet. Some improvement is needed in this code:- 1) Every File name should be written at first row in Column R of copied range. Later on New files will be added in folder and their data is needed to be pasted below current data in Active Sheet. 2) For data up-dation, code should copy data from only those files whose name is not present in Active Sheet in Column R. 3) If file name exists then send warning message, “Do you want to replace existing data of file (Name)?” If yes then code should replace existing data of this file else go to next file. 4) Paste Special Column width is needed once only when data of last file is pasted in active sheet. Avoid its repeating on each file. Kindly help in this matter. Thanks in advance. Regards, Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Consolidate Data.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: small issue
Format of B5:D5 is TEXT and B7:D7 is General. If Both rows will have same formats then you will get same answer & visa vera. On Wednesday, December 24, 2014 2:50:56 PM UTC+3, amar takale wrote: > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Expert,
You did an excellent job for me. Thanks you very much for this kindness and
I hope for the same support in future, always.
Regards,
Zafar Iqbal
On Tuesday, December 23, 2014 5:09:05 PM UTC+3, Vabz wrote:
>
> Hi
>
> Try this...
>
>
> Sub Data_Merge_From_All_Files_SelectFolder_NO_ADO()
> Dim bookList As Workbook
> Dim FileName As Variant
> Dim n As Long
> Dim disWB As Workbook
>
> Set disWB = ActiveWorkbook
> FileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
> *.xl*", MultiSelect:=True)
> Application.ScreenUpdating = False
>
> For n = LBound(FileName) To UBound(FileName)
> Set bookList = Workbooks.Open(FileName(n))
> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
> disWB.Worksheets(1).Activate
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
> Application.CutCopyMode = False
> bookList.Close
> Next n
> Application.ScreenUpdating = True
>
> MsgBox "Done!!"
>
> End Sub
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Tue, Dec 23, 2014 at 6:49 PM, Zafar Iqbal > wrote:
>
>> Dear Expert, your guidance is needed to solve another problem in this
>> code. I saved this code in Personal.xlb so that it should be available for
>> all new work books. When I open new work book & run code, it is not
>> updating active work book. Rather, it is up-dating data in personal.xlb. No
>> data is coming in active work book. How to fix this problem. Please help.
>> Thanks
>>
>>
>>
>> On Tuesday, 23 December 2014 12:33:13 UTC+3, Vabz wrote:
>>>
>>> Cheers!!
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>>
>>>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Expert, your guidance is needed to solve another problem in this code.
I saved this code in Personal.xlb so that it should be available for all
new work books. When I open new work book & run code, it is not updating
active work book. Rather, it is up-dating data in personal.xlb. No data is
coming in active work book. How to fix this problem. Please help. Thanks
On Tuesday, 23 December 2014 12:33:13 UTC+3, Vabz wrote:
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Tue, Dec 23, 2014 at 3:01 PM, Zafar Iqbal > wrote:
>
>> Excellent. Bundle of Thanks You did a great job ...
>>
>>
>> On Tuesday, 23 December 2014 12:27:24 UTC+3, Vabz wrote:
>>
>>> Try this then...
>>>
>>> Sub Data_Merge_From_All_Files_SelectFolder_NO_ADO()
>>> Dim bookList As Workbook
>>> Dim FileName As Variant
>>> Dim n As Long
>>>
>>> FileName = Application.GetOpenFilename(filefilter:="Excel Files
>>> (*.xl*), *.xl*", MultiSelect:=True)
>>> Application.ScreenUpdating = False
>>>
>>> For n = LBound(FileName) To UBound(FileName)
>>> Set bookList = Workbooks.Open(FileName(n))
>>> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
>>> ThisWorkbook.Worksheets(1).Activate
>>> Range("A1").Select
>>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
>>> SkipBlanks:=False, Transpose:=False
>>>
>>> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>>> Application.CutCopyMode = False
>>> bookList.Close
>>> Next n
>>> Application.ScreenUpdating = True
>>>
>>> MsgBox "Done!!"
>>>
>>> End Sub
>>>
>>>
>>> Cheers!!
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>> On Tue, Dec 23, 2014 at 12:49 PM, Zafar Iqbal wrote:
>>>
>>>> Dear Vaibhav Joshi, Now, It working excellently. It is fulfilling my
>>>> request's one part. Thanks for it. It is copying data from all files in
>>>> selected folder. Can you make it more flexible to select one or more
>>>> files with shift/ctrl keys, please?
>>>>
>>>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Excellent. Bundle of Thanks You did a great job ...
On Tuesday, 23 December 2014 12:27:24 UTC+3, Vabz wrote:
>
> Try this then...
>
> Sub Data_Merge_From_All_Files_SelectFolder_NO_ADO()
> Dim bookList As Workbook
> Dim FileName As Variant
> Dim n As Long
>
> FileName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
> *.xl*", MultiSelect:=True)
> Application.ScreenUpdating = False
>
> For n = LBound(FileName) To UBound(FileName)
> Set bookList = Workbooks.Open(FileName(n))
> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
> ThisWorkbook.Worksheets(1).Activate
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
> Application.CutCopyMode = False
> bookList.Close
> Next n
> Application.ScreenUpdating = True
>
> MsgBox "Done!!"
>
> End Sub
>
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Tue, Dec 23, 2014 at 12:49 PM, Zafar Iqbal > wrote:
>
>> Dear Vaibhav Joshi, Now, It working excellently. It is fulfilling my
>> request's one part. Thanks for it. It is copying data from all files in
>> selected folder. Can you make it more flexible to select one or more
>> files with shift/ctrl keys, please?
>>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Vaibhav Joshi, Now, It working excellently. It is fulfilling my request's one part. Thanks for it. It is copying data from all files in selected folder. Can you make it more flexible to select one or more files with shift/ctrl keys, please? --- On Tue, Dec 23, 2014 at 10:10 AM, Vaibhav Joshi wrote: > Hi > > Add this line before Set filesObj = dirObj.Files > Set dirObj = mergeObj.Getfolder(dirObj.self.Path) > > Cheers!! > > > + > *I did not do this for you. God is here working through me for you.* > > On Tue, Dec 23, 2014 at 12:40 AM, Zafar Iqbal wrote: > >> Dear Vaibhav Joshi, I checked it and found it working till selection of >> Folder after that it is stuck at line Set filesObj = dirObj.Files .Please >> help to fix this error mentioned below. Thanks >> >> Run-time error '438': >> Object does not support this property or method >> >> >> Regards, >> ZAFAR IQBAL >> --- >> > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Vaibhav Joshi, I checked it and found it working till selection of
Folder after that it is stuck at line Set filesObj = dirObj.Files .Please
help to fix this error mentioned below. Thanks
Run-time error '438':
Object does not support this property or method
Regards,
ZAFAR IQBAL
---
On Mon, Dec 22, 2014 at 5:20 PM, Vaibhav Joshi wrote:
> Ok
>
> Try this...
>
>
> Sub Data_Merge_From_All_Files()
> Dim bookList As Workbook
> Dim mergeObj, dirObj, filesObj, everyObj As Object
> Application.ScreenUpdating = False
> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>
> 'Set dirObj = mergeObj.Getfolder("D:\Test")
>
> Set objShell = CreateObject("Shell.Application")
> Set dirObj = objShell.BrowseForFolder(0, "Select Folder", 0, myStartFolder)
>
> Set filesObj = dirObj.Files
>
> For Each everyObj In filesObj
> If Right$(everyObj, 3) = "xls" Or Left$(Right$(everyObj, 4), 3) = "xls"
> Then
> Set bookList = Workbooks.Open(everyObj)
> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
> ThisWorkbook.Worksheets(1).Activate
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
> Application.CutCopyMode = False
> bookList.Close
> End If
> Next
> End Sub
>
>
> Cheers!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Mon, Dec 22, 2014 at 7:16 PM, Zafar Iqbal wrote:
>
>> Dear Vaibhav Joshi,
>>
>> I think my query was not well written so needs to repeat it. This code is
>> working fine for all files at fixed folder location written in code
>> as "D:\Test". I want to make it flexible. Instead of manually writing
>> folder location, File Open Dialog window should pop up enabling choose any
>> folder within driver & select single or multiple excel file for processing
>> by code. I think that if Application.GetOpenFilename(filefilter:="Excel
>> Files (*.xl*), *.xl*",MultiSelect:=True) is used properly it will give
>> this flexibilty. Please help. thanks
>>
>> Regards,
>> ZAFAR IQBAL
>> ---
>>
>> On Mon, Dec 22, 2014 at 4:23 PM, Vaibhav Joshi wrote:
>>
>>> Hi Zafar,
>>>
>>> Try this:
>>>
>>>
>>> Sub Data_Merge_From_All_Files()
>>> Dim bookList As Workbook
>>> Dim mergeObj, dirObj, filesObj, everyObj As Object
>>> Application.ScreenUpdating = False
>>> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>>>
>>> Set dirObj = mergeObj.Getfolder("D:\Test")
>>> Set filesObj = dirObj.Files
>>>
>>> For Each everyObj In filesObj
>>> If Right$(everyObj, 3) = "xls" Or Left$(Right$(everyObj, 4), 3) = "xls"
>>> Then
>>> Set bookList = Workbooks.Open(everyObj)
>>> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
>>> ThisWorkbook.Worksheets(1).Activate
>>> Range("A1").Select
>>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
>>> SkipBlanks:=False, Transpose:=False
>>>
>>> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>>> Application.CutCopyMode = False
>>> bookList.Close
>>> End If
>>> Next
>>> End Sub
>>>
>>>
>>> Cheers!!
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>> On Mon, Dec 22, 2014 at 12:32 PM, Zafar Iqbal
>>> wrote:
>>>
>>>> Dear Experts,
>>>> Below mentioned code is to copy data from Sheet1 of all files at
>>>> specified location and paste in active Sheet. For regular up-dation on
>>>> weekly basis, it is needed to make this macro flexible with use of ADO
>>>> Connection. It will allow us to choose required folder, select one or more
>>>> latest files with shift/control button and get data pasted below last row
>>>> of used range of current sheet. I tried to use below ADO option but could
>>>> not get success. Please help to use it in below mentioned code. Thanks
>>>>
>>>> Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
>>>> *.xl*",MultiSelect:=True)
>>>&g
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Vaibhav Joshi,
I think my query was not well written so needs to repeat it. This code is
working fine for all files at fixed folder location written in code
as "D:\Test". I want to make it flexible. Instead of manually writing
folder location, File Open Dialog window should pop up enabling choose any
folder within driver & select single or multiple excel file for processing
by code. I think that if Application.GetOpenFilename(filefilter:="Excel
Files (*.xl*), *.xl*",MultiSelect:=True) is used properly it will give this
flexibilty. Please help. thanks
Regards,
ZAFAR IQBAL
---
On Mon, Dec 22, 2014 at 4:23 PM, Vaibhav Joshi wrote:
> Hi Zafar,
>
> Try this:
>
>
> Sub Data_Merge_From_All_Files()
> Dim bookList As Workbook
> Dim mergeObj, dirObj, filesObj, everyObj As Object
> Application.ScreenUpdating = False
> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>
> Set dirObj = mergeObj.Getfolder("D:\Test")
> Set filesObj = dirObj.Files
>
> For Each everyObj In filesObj
> If Right$(everyObj, 3) = "xls" Or Left$(Right$(everyObj, 4), 3) = "xls"
> Then
> Set bookList = Workbooks.Open(everyObj)
> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
> ThisWorkbook.Worksheets(1).Activate
> Range("A1").Select
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
>
> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
> Application.CutCopyMode = False
> bookList.Close
> End If
> Next
> End Sub
>
>
> Cheers!!
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Mon, Dec 22, 2014 at 12:32 PM, Zafar Iqbal wrote:
>
>> Dear Experts,
>> Below mentioned code is to copy data from Sheet1 of all files at
>> specified location and paste in active Sheet. For regular up-dation on
>> weekly basis, it is needed to make this macro flexible with use of ADO
>> Connection. It will allow us to choose required folder, select one or more
>> latest files with shift/control button and get data pasted below last row
>> of used range of current sheet. I tried to use below ADO option but could
>> not get success. Please help to use it in below mentioned code. Thanks
>>
>> Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
>> *.xl*",MultiSelect:=True)
>>
>> -
>>
>> Sub Data_Merge_From_All_Files()
>> Dim bookList As Workbook
>> Dim mergeObj, dirObj, filesObj, everyObj As Object
>> Application.ScreenUpdating = False
>> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>>
>> Set dirObj = mergeObj.Getfolder("D:\Test")
>> Set filesObj = dirObj.Files
>> For Each everyObj In filesObj
>> Set bookList = Workbooks.Open(everyObj)
>>
>> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
>> ThisWorkbook.Worksheets(1).Activate
>> Range("A1").Select
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>>
>> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>> Application.CutCopyMode = False
>> bookList.Close
>> Next
>> End Sub
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 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) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.c
Re: $$Excel-Macros$$ How to remove excel macros from multiple closed files
Now, It is working excellent. Thanks for this great help, dear experts. --- On Mon, Dec 22, 2014 at 1:32 PM, Vaibhav Joshi wrote: > Try this...Removed some bugs, now its working good... > > Cheers!! > > + > *I did not do this for you. God is here working through me for you.* > > On Mon, Dec 22, 2014 at 3:26 PM, Vaibhav Joshi wrote: > >> Hi Zafar, >> >> Pls let us know error, before that .. >> >> Trust Access To Visual Basics Project must be enabled. >> >> From Excel: Tools | Macro | Security | Trusted Sources.. >> >> or Excel Options>Macro Settings>Developer Macro Settings, Tick Trust >> Access to the VBA object >> >> Try this file.. >> >> Cheers!! >> >> + >> > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Use of ADO Connection in Excel Macro
Dear Mandeep Baluja, I want this "file dialog method" to be added in below
mentioned code. I tried it with hit & trial but could not succeed. Please
add this command at suitable position with related parameters in below
code. Thanks
On Monday, December 22, 2014 12:32:54 PM UTC+3, Mandeep Baluja wrote:
>
> Open sheet 1 by 1 with the help of macro use file dilaog method to get
> the names of the files and save it in xls Extension.
>
> Regards,
> Mandeep Baluja
> LearningZmyPassion
> https://www.linkedin.com/profile/view?id=312532939
> https://www.facebook.com/VBAEXCELSQL?ref=hl
>
>
>
>
>
> On Monday, December 22, 2014 12:32:37 PM UTC+5:30, Zafar Iqbal wrote:
>>
>> Dear Experts,
>> Below mentioned code is to copy data from Sheet1 of all files at
>> specified location and paste in active Sheet. For regular up-dation on
>> weekly basis, it is needed to make this macro flexible with use of ADO
>> Connection. It will allow us to choose required folder, select one or more
>> latest files with shift/control button and get data pasted below last row
>> of used range of current sheet. I tried to use below ADO option but could
>> not get success. Please help to use it in below mentioned code. Thanks
>>
>> Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
>> *.xl*",MultiSelect:=True)
>>
>> -
>>
>> Sub Data_Merge_From_All_Files()
>> Dim bookList As Workbook
>> Dim mergeObj, dirObj, filesObj, everyObj As Object
>> Application.ScreenUpdating = False
>> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>>
>> Set dirObj = mergeObj.Getfolder("D:\Test")
>> Set filesObj = dirObj.Files
>> For Each everyObj In filesObj
>> Set bookList = Workbooks.Open(everyObj)
>>
>> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
>> ThisWorkbook.Worksheets(1).Activate
>> Range("A1").Select
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>>
>> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>> Application.CutCopyMode = False
>> bookList.Close
>> Next
>> End Sub
>>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ How to remove excel macros from multiple closed files
Right now, I tried it and getting error message. Let me check possible corrections at my end. Thanks for this kindness. Regards, ZAFAR IQBAL --- On Mon, Dec 22, 2014 at 12:19 PM, Vaibhav Joshi wrote: > Dear Ashish > > Your Code is working fine.. > > > Dear Zafar, > > Check this file, i have modified Ashish Kumar's code to suit your need,,, > > Cheers! > > + > *I did not do this for you. God is here working through me for you.* > > On Mon, Dec 22, 2014 at 2:13 PM, Zafar Iqbal wrote: > >> Dear Vaibhav Joshi, all files are with extension xls. If code will >> temporarily or permanently do something with extension, it is no harm to >> me. I can handle both .xls and .xlsx without any difficulty. Thanks >> >> Regards, >> ZAFAR IQBAL >> --- >> >> On Mon, Dec 22, 2014 at 11:36 AM, Vaibhav Joshi wrote: >> >>> Hi Zafar, >>> >>> If this is not as per your requirement then macro can be made as per >>> your need.. >>> >>> Coz.. .xlsx file extension is not supported in older version then in >>> that case this solution will not work.. >>> >>> Do let us know if you want .xls extension post removal of VBE. >>> >>> Cheers!! >>> >>> + >>> *I did not do this for you. God is here working through me for you.* >>> >>> On Mon, Dec 22, 2014 at 1:58 PM, Zafar Iqbal wrote: >>> >>>> If there is no short cut then I will follow your suggestion. Thanks for >>>> your guidance. >>>> >>>> On Sunday, December 21, 2014 11:16:34 AM UTC+3, Vabz wrote: >>>>> >>>>> Try to open file one by one using macro n save it as macro free >>>>> workbook . Xlsx >>>>> >>>>> Cheers >>>>> On Dec 21, 2014 1:03 PM, "Zafar Iqbal" wrote: >>>>> >>>>>> I have +500 Excel files (*.xls) having macros, all located in same >>>>>> folder. I want to remove all macros from these files. Removing macros >>>>>> manually one by one from these files will take too much time. Is it >>>>>> possible to create a new macro in a separate excel file which will remove >>>>>> all macros from these closed files? Thanks for your guidance in advance. >>>>>> Regards, >>>>>> Zafar Iqbal >>>>>> >>>>> -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ How to remove excel macros from multiple closed files
Dear Vaibhav Joshi, all files are with extension xls. If code will temporarily or permanently do something with extension, it is no harm to me. I can handle both .xls and .xlsx without any difficulty. Thanks Regards, ZAFAR IQBAL --- On Mon, Dec 22, 2014 at 11:36 AM, Vaibhav Joshi wrote: > Hi Zafar, > > If this is not as per your requirement then macro can be made as per your > need.. > > Coz.. .xlsx file extension is not supported in older version then in that > case this solution will not work.. > > Do let us know if you want .xls extension post removal of VBE. > > Cheers!! > > + > *I did not do this for you. God is here working through me for you.* > > On Mon, Dec 22, 2014 at 1:58 PM, Zafar Iqbal wrote: > >> If there is no short cut then I will follow your suggestion. Thanks for >> your guidance. >> >> On Sunday, December 21, 2014 11:16:34 AM UTC+3, Vabz wrote: >>> >>> Try to open file one by one using macro n save it as macro free >>> workbook . Xlsx >>> >>> Cheers >>> On Dec 21, 2014 1:03 PM, "Zafar Iqbal" wrote: >>> >>>> I have +500 Excel files (*.xls) having macros, all located in same >>>> folder. I want to remove all macros from these files. Removing macros >>>> manually one by one from these files will take too much time. Is it >>>> possible to create a new macro in a separate excel file which will remove >>>> all macros from these closed files? Thanks for your guidance in advance. >>>> Regards, >>>> Zafar Iqbal >>>> >>>> -- >>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? >>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>> https://www.facebook.com/discussexcel >>>> >>>> FORUM RULES >>>> >>>> 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) Jobs posting is not allowed. >>>> 6) Sharing copyrighted material and their links is not allowed. >>>> >>>> NOTE : Don't ever post confidential data in a workbook. Forum owners >>>> and members are not responsible for any loss. >>>> --- >>>> You received this message because you are subscribed to the Google >>>> Groups "MS EXCEL AND VBA MACROS" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to excel-macros...@googlegroups.com. >>>> To post to this group, send email to excel-...@googlegroups.com. >>>> Visit this group at http://groups.google.com/group/excel-macros. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 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) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >> To post to this group, send email to excel-macros@googlegroups.com. >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > -- >
Re: $$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Expert, Sorry for it. I replied to query "Rotate Picture in Cell
Comments" & appreciated your efforts. However, for query "Remove all Macros
in multiple files", I was hoping to get help from someone else in the forum
so delayed my response to your reply. I will follow forum rules & send you
response ASAP. Thanks
Regards,
ZAFAR IQBAL
---
On Mon, Dec 22, 2014 at 11:25 AM, Vaibhav Joshi wrote:
> Hi
>
> You did not acknowledged response on last Query!!
>
> Read rule # 4) Acknowledge the responses you receive, good or bad.
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Mon, Dec 22, 2014 at 12:32 PM, Zafar Iqbal wrote:
>
>> Dear Experts,
>> Below mentioned code is to copy data from Sheet1 of all files at
>> specified location and paste in active Sheet. For regular up-dation on
>> weekly basis, it is needed to make this macro flexible with use of ADO
>> Connection. It will allow us to choose required folder, select one or more
>> latest files with shift/control button and get data pasted below last row
>> of used range of current sheet. I tried to use below ADO option but could
>> not get success. Please help to use it in below mentioned code. Thanks
>>
>> Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
>> *.xl*",MultiSelect:=True)
>>
>> -
>>
>> Sub Data_Merge_From_All_Files()
>> Dim bookList As Workbook
>> Dim mergeObj, dirObj, filesObj, everyObj As Object
>> Application.ScreenUpdating = False
>> Set mergeObj = CreateObject("Scripting.FileSystemObject")
>>
>> Set dirObj = mergeObj.Getfolder("D:\Test")
>> Set filesObj = dirObj.Files
>> For Each everyObj In filesObj
>> Set bookList = Workbooks.Open(everyObj)
>>
>> Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
>> ThisWorkbook.Worksheets(1).Activate
>> Range("A1").Select
>> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
>> SkipBlanks:=False, Transpose:=False
>>
>> Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
>> Application.CutCopyMode = False
>> bookList.Close
>> Next
>> End Sub
>>
>> --
>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES
>>
>> 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) Jobs posting is not allowed.
>> 6) Sharing copyrighted material and their links is not allowed.
>>
>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>> members are not responsible for any loss.
>> ---
>> You received this message because you are subscribed to the Google Groups
>> "MS EXCEL AND VBA MACROS" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to excel-macros+unsubscr...@googlegroups.com.
>> To post to this group, send email to excel-macros@googlegroups.com.
>> Visit this group at http://groups.google.com/group/excel-macros.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 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) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to a topi
Re: $$Excel-Macros$$ Re: Excel Macro to add rotated picture in Cell Comments
Well done, Thanks
On Wednesday, December 17, 2014 3:41:22 PM UTC+3, Vabz wrote:
>
> hi
>
> can you share your experience with us?
>
>
> +
> *I did not do this for you. God is here working through me for you.*
>
> On Tue, Dec 16, 2014 at 2:14 PM, Vaibhav Joshi
> > wrote:
>>
>> Hey Zafar,
>>
>> You can try this code...
>>
>> Hope this helps / you can also share any alternate method you have found
>>
>> Cheers!!
>>
>> +
>> *I did not do this for you. God is here working through me for you.*
>>
>> On Mon, Dec 15, 2014 at 6:23 PM, Vaibhav Joshi > > wrote:
>>>
>>> Yes, you need to rotate picture first & then insert..
>>>
>>> http://stackoverflow.com/questions/21001667/rotate-a-saved-image-with-vba
>>>
>>> check link for help.
>>>
>>> +
>>> *I did not do this for you. God is here working through me for you.*
>>>
>>> On Sun, Dec 14, 2014 at 1:11 AM, Zafar Iqbal >> > wrote:
>>>>
>>>> For a quick reference,sample file is attached. I tried
>>>> .Comment.Shape.Rotation = 270# but failed to get the result. I got Error
>>>> Message "Permission Denied". I think picture should be pasted & rotated at
>>>> temporary location or in memory and then called back & pasted in cell
>>>> comment. Thanks for your time to solve my query.
>>>>
>>>> Regards,
>>>> ZAFAR IQBAL
>>>> Al Zamil, Jeddah, KSA
>>>> Office: +966 12 6380 766 -x- 313, Cell: +966 554 789 391
>>>> ---
>>>>
>>>> On Sat, Dec 13, 2014 at 7:27 PM, Zafar Iqbal >>> > wrote:
>>>>>
>>>>> Dear Experts,
>>>>> I got a Excel macro to paste pictures in Excel cell comments. It is
>>>>> working fine. Now, I need a change in it. Pictures should be rotated 270
>>>>> degree & then pasted in cell comments. Can anybody help, please? Thanks
>>>>> in
>>>>> advance. code is mentioned below:-
>>>>>
>>>>> Sub InsertPictures()
>>>>> Dim cll As Range
>>>>> Dim Rng As Range
>>>>> Dim strPath As String
>>>>> strPath = "D:\Photo Folder"
>>>>> With Sheets("Sheet1")
>>>>> Set Rng = Range("A2:A416")
>>>>> End With
>>>>> For Each cll In Rng
>>>>> If Dir$(strPath & "\" & cll.Value & ".jpg") <> "" Then
>>>>> With cll
>>>>> .ClearComments
>>>>> .AddComment ("")
>>>>> .Comment.Shape.Fill.UserPicture (strPath & "\" &
>>>>> cll.Value & ".jpg")
>>>>> .Comment.Shape.Height = 160
>>>>> .Comment.Shape.Width = 120
>>>>> .Comment.Shape.LockAspectRatio = msoTrue
>>>>> End With
>>>>> End If
>>>>> Next cll
>>>>> End Sub
>>>>>
>>>> --
>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
>>>> It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>> https://www.facebook.com/discussexcel
>>>>
>>>> FORUM RULES
>>>>
>>>> 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) Jobs posting is not allowed.
>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>
>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners
>>>> and members are not responsible for any loss.
>>>> ---
>>>> You received this message because you are subscribed to the Google
>>>> Groups "MS EXCEL AND VBA MACROS" group.
>>>> To unsubscribe
Re: $$Excel-Macros$$ How to remove excel macros from multiple closed files
If there is no short cut then I will follow your suggestion. Thanks for your guidance. On Sunday, December 21, 2014 11:16:34 AM UTC+3, Vabz wrote: > > Try to open file one by one using macro n save it as macro free workbook > . Xlsx > > Cheers > On Dec 21, 2014 1:03 PM, "Zafar Iqbal" > > wrote: > >> I have +500 Excel files (*.xls) having macros, all located in same >> folder. I want to remove all macros from these files. Removing macros >> manually one by one from these files will take too much time. Is it >> possible to create a new macro in a separate excel file which will remove >> all macros from these closed files? Thanks for your guidance in advance. >> Regards, >> Zafar Iqbal >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 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) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros...@googlegroups.com . >> To post to this group, send email to excel-...@googlegroups.com >> . >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Use of ADO Connection in Excel Macro
Dear Experts,
Below mentioned code is to copy data from Sheet1 of all files at specified
location and paste in active Sheet. For regular up-dation on weekly basis,
it is needed to make this macro flexible with use of ADO Connection. It
will allow us to choose required folder, select one or more latest files
with shift/control button and get data pasted below last row of used range
of current sheet. I tried to use below ADO option but could not get
success. Please help to use it in below mentioned code. Thanks
Application.GetOpenFilename(filefilter:="Excel Files (*.xl*),
*.xl*",MultiSelect:=True)
-
Sub Data_Merge_From_All_Files()
Dim bookList As Workbook
Dim mergeObj, dirObj, filesObj, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set dirObj = mergeObj.Getfolder("D:\Test")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("A2:Q50" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ How to remove excel macros from multiple closed files
I have +500 Excel files (*.xls) having macros, all located in same folder. I want to remove all macros from these files. Removing macros manually one by one from these files will take too much time. Is it possible to create a new macro in a separate excel file which will remove all macros from these closed files? Thanks for your guidance in advance. Regards, Zafar Iqbal -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Re: Excel Macro to add rotated picture in Cell Comments
Dear Expert, You did an excellent Job. Thanks for it. On Wednesday, 17 December 2014 15:41:22 UTC+3, Vabz wrote: > > hi > > can you share your experience with us? > > > + > *I did not do this for you. God is here working through me for you.* > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 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) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Excel Macro to add rotated picture in Cell Comments
Dear Experts,
I got a Excel macro to paste pictures in Excel cell comments. It is working
fine. Now, I need a change in it. Pictures should be rotated 270 degree &
then pasted in cell comments. Can anybody help, please? Thanks in advance.
code is mentioned below:-
Sub InsertPictures()
Dim cll As Range
Dim Rng As Range
Dim strPath As String
strPath = "D:\Photo Folder"
With Sheets("Sheet1")
Set Rng = Range("A2:A416")
End With
For Each cll In Rng
If Dir$(strPath & "\" & cll.Value & ".jpg") <> "" Then
With cll
.ClearComments
.AddComment ("")
.Comment.Shape.Fill.UserPicture (strPath & "\" & cll.Value
& ".jpg")
.Comment.Shape.Height = 160
.Comment.Shape.Width = 120
.Comment.Shape.LockAspectRatio = msoTrue
End With
End If
Next cll
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
$$Excel-Macros$$ Re: Conditional Formatting with Icons
During testing I changed data in cell A1 of attached Sheet. Please assume
A1=50 then text written in attached sheet will give true picture of my
query. In short, I want that if value in B1 is => A1 then conditional
format should make it ticked with OK, if B1<= A1 then ticked with sign !
otherwise with sign x. Main bottle neck is copy paste of this conditional
formatting from one cell to many other cells. Thanks for your help in
advance.
On Saturday, 8 February 2014 07:49:00 UTC+3, Zafar Iqbal wrote:
>
> Dear Experts,
>
> In Excel version 2007, I am trying to get conditional formatting with
> Icons. In this option targeted cell reference must be absolute like $A$1
> and it does not allow A1. Absolute reference becomes bottle neck for copy
> paste of this conditional formatting from one cell to others. I tried to
> use formula Indirect("A"&Row()) in conditional formatting dialog. It is
> strange for me that instead of linking to the same row it is checking data
> of one row below in the same column. When I checked the same formula in
> version 2010, it is working well. Sample file is attached. Please help in
> this matter and advise alternate options/formula to get Conditional
> Formatting with Icons and its copy paste to other bunch of many cells.
> Thanks
>
> Regards,
> ZAFAR IQBAL
> ---
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
Re: $$Excel-Macros$$ Paste Special Data with 2 decimals (Permanently)
I pasted this line in macro last portion as mentioned below:-
Sheets(“sheet1”).UsedRange.Value = Sheets(“sheet1”).UsedRange.Value
Sheets("Sheet1").Copy
End Sub
I am getting message Complie Error: Variable not defined with yellow
selection at Sheet1.
Please help in this matter. Thanks
Regrads,
Zafar iqbal
On Jan 23, 7:35 pm, "dguillett1" wrote:
> Oops. I also should have added this to change formulas to values before copy
>
> sheets(“sheet1”).usedrange.value=sheets(“sheet1”).usedrange.value
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
> From: Sam Mathai Chacko
> Sent: Monday, January 23, 2012 9:21 AM
> To: excel-macros@googlegroups.com
> Subject: Re: $$Excel-Macros$$ Paste Special Data with 2 decimals (Permanently)
>
> From what Don intended, I believe it is
>
> Dim i as long, lr as long, lc as long, mf as range
>
> Regards,
>
> Sam Mathai Chacko
>
> On Mon, Jan 23, 2012 at 8:47 PM, Zafar Iqbal wrote:
>
--
FORUM RULES (986+ 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$$ Paste Special Data with 2 decimals (Permanently)
Sir, Thanks for it. Just please guide me about i, lr, lc and mf. I
think that first three are to be defined as integer. What will be mf?
macro will run when we define these items.
Regards,
Zafar Iqbal
On Jan 23, 6:07 pm, Sam Mathai Chacko wrote:
> Zafar, your numbers are all coming correct. For the date format, all you
> need to do is format the series to "d-mmm"
>
> Why do you need a macro for that? Keep the custom format, and it will
> always remain.
>
> Sam Mathai Chacko
>
>
>
>
>
> On Mon, Jan 23, 2012 at 8:15 PM, Zafar Iqbal wrote:
> > We have to define i, lr, lc and mf. I tried with Dim i as integer, Dim
> > lr as Inetger, Dim lc as Integer. But what is mf? I do not know.
> > Please help. Thanks
>
> > On Jan 23, 5:26 pm, "dguillett1" wrote:
> > > Finds last row and last column and formats range and copies sheet1 to new
> > > book without formulas.
> > > Sub CopyShtwithTWOdecimals()
> > > For i = 1 To Sheets.Count
> > > With Sheets(i)
> > > lr = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
> > > , , , xlByRows, xlPrevious).Row
> > > lc = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
> > > , , , xlByColumns, xlPrevious).Column
> > > Set mf = .Cells.Find(What:="Activity", LookIn:=xlValues, _
> > > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > > MatchCase:=False)
> > > If Not mf Is Nothing Then
> > > .Range(.Cells(mf.Row, 1), .Cells(lr, lc)).NumberFormat = "0.00"
> > > End If
> > > End With
> > > Next
> > > Sheets("Sheet1").Copy
> > > End Sub
>
> > > Don Guillett
> > > SalesAid Software
> > > dguille...@gmail.com
>
--
FORUM RULES (986+ 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$$ Paste Special Data with 2 decimals (Permanently)
We have to define i, lr, lc and mf. I tried with Dim i as integer, Dim
lr as Inetger, Dim lc as Integer. But what is mf? I do not know.
Please help. Thanks
On Jan 23, 5:26 pm, "dguillett1" wrote:
> Finds last row and last column and formats range and copies sheet1 to new
> book without formulas.
> Sub CopyShtwithTWOdecimals()
> For i = 1 To Sheets.Count
> With Sheets(i)
> lr = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
> , , , xlByRows, xlPrevious).Row
> lc = .Cells.Find("*", Cells(Rows.Count, Columns.Count) _
> , , , xlByColumns, xlPrevious).Column
> Set mf = .Cells.Find(What:="Activity", LookIn:=xlValues, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not mf Is Nothing Then
> .Range(.Cells(mf.Row, 1), .Cells(lr, lc)).NumberFormat = "0.00"
> End If
> End With
> Next
> Sheets("Sheet1").Copy
> End Sub
>
> Don Guillett
> SalesAid Software
> dguille...@gmail.com
>
--
FORUM RULES (986+ 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$$ Paste Special Data with 2 decimals (Permanently)
Sir, Now, graph table is more correct. Last thing remaining is date format in graph. Right now, In graph table, date format (23-Jan) is converted to number format (40931). I need 23-Jan instead of 40931. Regards, Zafar Iqbal On Jan 23, 4:57 pm, Sam Mathai Chacko wrote: > Well, this should round it. Replace it in the file I sent earlier. > > Sub FormatNumbersTo2Decimals() > > Dim wks As Worksheet > Dim rng As Range > > For Each wks In ThisWorkbook.Worksheets > For Each rng In wks.Cells.SpecialCells(2, 1) > If IsNumeric(rng.Text) Then > rng.NumberFormat = "0.00" > rng.Value = Val(rng.Text) > End If > Next > For Each rng In wks.Cells.SpecialCells(-4123, 1) > If IsNumeric(rng.Text) Then > rng.NumberFormat = "0.00" > *rng.Value = Val(rng.Text)* > End If > Next > Next > > End Sub > > Sam Mathai Chacko > > > > > > On Mon, Jan 23, 2012 at 7:04 PM, Zafar Iqbal wrote: > > Dear Sir, > > I have already tried it. Yet, as per your instructions, I did the same > > again. There are two problems. > > First is that decimals in print view are surely two only but in actual > > it may remains more than two in background. If we increase decimals > > places it will be viewable, again. I need result ROUND two decimals > > permanently. > > Second problem is about graph. On copy paste, its data table figures > > remained with more than two decimals and date values format get > > converted to number format. So, 23-Jan-2012 becomes 23931 and so on. > > How to make this 23931 back to 23-Jan? Also, I do not know how to > > paste special FORMAT on graph. > > > Please help. Thanks > > > Regards, > > Zafar Iqbal > > > On Jan 23, 4:01 pm, NOORAIN ANSARI wrote: > >> Dear Zafar, > > >> First copy and value paste in New Sheet.. > >> Again copy and Select data of new sheet and Format paste . > > >> Hope it will help to you. > > >> -- > >> Thanks & regards, > >> Noorain Ansari > >> *http://excelmacroworld.blogspot.com/*< > > http://excelmacroworld.blogspot.com/> > > > > >> *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/ > > >> On Mon, Jan 23, 2012 at 2:28 PM, Zafar Iqbal wrote: > >> > Dear Experts, > > >> > Attached is a Sample file Excel-2003 with 03 Sheets linked with each > >> > others. Sheet1 is linked with data from Sheet2 by Hlookup / index > formulas > >> > and it has Data Validation at Cell H1, too. Chart in this Sheet1 is > also > >> > linked with Data at Sheet2. > > >> > Sheet3 will get data by Paste Special from MS Project file which > results > >> > in figures with more than 2 decimals. We need to e-mail only Sheet1 > (with > >> > Data + Chart) to Management without any formula. We make copy of > Sheet1 and > >> > save it in new file. Resultant file has chart and data with more than 2 > >> > decimals. Please provide us macro which will permanently round data to > 2 > >> > decimals in Sheet + Chart and it will not disturb cells having text > >> > values coming from Hlookup, Index, etc. > > >> > Thanks for your guidance, in advance. > > >> > Regards, > >> > ZAFAR IQBAL > > > -- > > FORUM RULES (986+ 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- Hide quoted text - > > - Show quoted text -- Hide quoted text - > > - Sho
Re: $$Excel-Macros$$ Paste Special Data with 2 decimals (Permanently)
Dear Mr. *Sam Mathai Chacko,* Your macro works fine in parent file. When I copy Sheet1 only to new file, data in graph table needs a little attention. Dates are converted to numbers (23-Jan-2012 becomes 40931 and so on). Macro converted grapgh data to 2 decimals in parent file. However, in copied new file figures in graph table are again not upto 2 decimals. File is attached. Please help. Thanks Regards, ZAFAR IQBAL -- FORUM RULES (986+ 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 Book41.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Paste Special Data with 2 decimals (Permanently)
Dear Sir, I have already tried it. Yet, as per your instructions, I did the same again. There are two problems. First is that decimals in print view are surely two only but in actual it may remains more than two in background. If we increase decimals places it will be viewable, again. I need result ROUND two decimals permanently. Second problem is about graph. On copy paste, its data table figures remained with more than two decimals and date values format get converted to number format. So, 23-Jan-2012 becomes 23931 and so on. How to make this 23931 back to 23-Jan? Also, I do not know how to paste special FORMAT on graph. Please help. Thanks Regards, Zafar Iqbal On Jan 23, 4:01 pm, NOORAIN ANSARI wrote: > Dear Zafar, > > First copy and value paste in New Sheet.. > Again copy and Select data of new sheet and Format paste . > > Hope it will help to you. > > -- > Thanks & regards, > Noorain Ansari > *http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/> > *http://noorain-ansari.blogspot.com/*<http://noorain-ansari.blogspot.com/> > > > > On Mon, Jan 23, 2012 at 2:28 PM, Zafar Iqbal wrote: > > Dear Experts, > > > Attached is a Sample file Excel-2003 with 03 Sheets linked with each > > others. Sheet1 is linked with data from Sheet2 by Hlookup / index formulas > > and it has Data Validation at Cell H1, too. Chart in this Sheet1 is also > > linked with Data at Sheet2. > > > Sheet3 will get data by Paste Special from MS Project file which results > > in figures with more than 2 decimals. We need to e-mail only Sheet1 (with > > Data + Chart) to Management without any formula. We make copy of Sheet1 and > > save it in new file. Resultant file has chart and data with more than 2 > > decimals. Please provide us macro which will permanently round data to 2 > > decimals in Sheet + Chart and it will not disturb cells having text > > values coming from Hlookup, Index, etc. > > > Thanks for your guidance, in advance. > > > Regards, > > ZAFAR IQBAL -- FORUM RULES (986+ 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$$ Paste Special Data with 2 decimals (Permanently)
Dear Experts, Attached is an Sample file Excel-2003 with 03 Sheets linked with each others. Sheet1 is linked with data from Sheet2 by Hlookup / index formulas and it has Data Validation at Cell H1, too. Chart in this Sheet1 is also linked with Data at Sheet2. Sheet3 will get data by Paste Special from MS Project file which results in figures with more than 2 decimals. We need to e-mail only Sheet1 (with Data + Chart) to Management without any formula. We make copy of Sheet1 and save it in new file. Resultant file has chart and data with more than 2 decimals. Please provide us macro which will permanently round data to 2 decimals in Sheet + Chart and it will not disturb cells having text values coming from Hlookup, Index, etc. Thanks for your guidance, in advance. Regards, ZAFAR IQBAL -- FORUM RULES (986+ 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 Paste Special with 2 decimals.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$
Dear Expert, Will you please explain why to mutiply date value with 100? Regards, Zafar Iqbal On Thu, Sep 15, 2011 at 2:53 PM, §»VIPER«§ wrote: > hi > > pfa > > > -- > *Great day,* > *viper* > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Conditional Formatting
Dear Experts, Will somebody inform me that why my request is not getting your kind attention, please? Regards, Zafar Iqbal On Mon, Sep 5, 2011 at 11:00 AM, Zafar Iqbal wrote: > Dear Experts, > > Still waiting for your kind support. > > Regards, > Zafar Iqbal > > On Sun, Sep 4, 2011 at 8:11 AM, Zafar Iqbal wrote: > >> Dear Experts, >> >> Still waiting for your kind response. >> >> Regards, >> Zafar Iqbal >> ----- >> On Mon, Aug 29, 2011 at 12:59 PM, Zafar Iqbal wrote: >> >>> Dear Expert, >>> >>> One last thing to remind that Color bar should increase or decrease when >>> duration is changed. Please help in this matter with Excel logical formulas >>> & conditional formatting, only. Try to avoid VBA, if possible. Thanks >>> >>> Regards, >>> Zafar Iqbal >>> >>> On Mon, Aug 29, 2011 at 11:03 AM, Zafar Iqbal wrote: >>> >>>> Dear Expert, >>>> >>>> Thanks for sparing time for my question. You are right in indicating >>>> five conditions. >>>> >>>> Case Result Cell Color >>>> Condition-1 0 NoIf Dur1,2,3 is Zero or blank >>>> Condition-2 1 Green If Dur1>0 >>>> Condition-3 2 Yellow If Dur2>0 >>>> Condition-4 3 Blue If Dur3>0 >>>> Condition-5 x No If WO1,2,3 = On Leave >>>> I shall be very thankful to you for this great help to solve this >>>> problem. >>>> >>>> Regards, >>>> >>>> Zafar Iqbal >>>> - >>>> >>> -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Conditional Formatting
Dear Experts, Still waiting for your kind response. Regards, Zafar Iqbal - On Mon, Aug 29, 2011 at 12:59 PM, Zafar Iqbal wrote: > Dear Expert, > > One last thing to remind that Color bar should increase or decrease when > duration is changed. Please help in this matter with Excel logical formulas > & conditional formatting, only. Try to avoid VBA, if possible. Thanks > > Regards, > Zafar Iqbal > > On Mon, Aug 29, 2011 at 11:03 AM, Zafar Iqbal wrote: > >> Dear Expert, >> >> Thanks for sparing time for my question. You are right in indicating five >> conditions. >> >> Case Result Cell Color >> Condition-1 0 NoIf Dur1,2,3 is Zero or blank >> Condition-2 1 Green If Dur1>0 >> Condition-3 2 Yellow If Dur2>0 >> Condition-4 3 Blue If Dur3>0 >> Condition-5 x No If WO1,2,3 = On Leave >> I shall be very thankful to you for this great help to solve this problem. >> >> Regards, >> >> Zafar Iqbal >> - >> >> On Sun, Aug 28, 2011 at 8:36 PM, XLS S wrote: >> >>> hey Zafar, >>> >>> i think there is 5 condition... >>> 1st if result is 1 >>> 2nd if result is 2 >>> 3rd if result is 3 >>> 4th if result is 0 >>> and 5 is if On Leave >>> >>> if is it ok then give the confirmation, then i start the work >> >> -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Conditional Formatting
Dear Expert, One last thing to remind that Color bar should increase or decrease when duration is changed. Please help in this matter with Excel logical formulas & conditional formatting, only. Try to avoid VBA, if possible. Thanks Regards, Zafar Iqbal On Mon, Aug 29, 2011 at 11:03 AM, Zafar Iqbal wrote: > Dear Expert, > > Thanks for sparing time for my question. You are right in indicating five > conditions. > > Case Result Cell Color > Condition-1 0 NoIf Dur1,2,3 is Zero or blank > Condition-2 1 Green If Dur1>0 > Condition-3 2 Yellow If Dur2>0 > Condition-4 3 Blue If Dur3>0 > Condition-5 x No If WO1,2,3 = On Leave > I shall be very thankful to you for this great help to solve this problem. > > Regards, > > Zafar Iqbal > - > > On Sun, Aug 28, 2011 at 8:36 PM, XLS S wrote: > >> hey Zafar, >> >> i think there is 5 condition... >> 1st if result is 1 >> 2nd if result is 2 >> 3rd if result is 3 >> 4th if result is 0 >> and 5 is if On Leave >> >> if is it ok then give the confirmation, then i start the work > > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Conditional Formatting
Dear Experts,
I am still waiting for reply from you all. Please help me in this matter.
Thanks
Regards,
Zafar Iqbal
On Sat, Aug 27, 2011 at 10:29 AM, Zafar Iqbal wrote:
> Dear Expert,
>
> Ref your instructions, please find attached file with conditional
> formatting applied. My worry is about 4th condition "On Leave". My
> conditional formatting gets failed when employee is "On Leave". This may
> occur anywhere in
> Assignment-1, 2 or 3.
> On Leave condition should be mentioned as "x" without any color.
> Color pattern should not be disturbed whenever "On Leave" condition is
> applied. Assignment-1 (Light Green), Assignement 2 - (Light Yellow) and
> assignment 3 with (Light Blue), always.
>
> Please help me in this problem with formula only.
>
> Thanks,
>
> Zafar Iqbal
>
> On Thu, Aug 25, 2011 at 2:17 PM, XLS S wrote:
>
>> kindly put 3 condition it self and then send file.
>>
>> try this code
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Set I = Intersect(Target, Range("B2:B8"))
>> If Not I Is Nothing Then
>> Select Case Target
>> Case 0 To 100: NewColor = 37 ' light blue
>> Case 101 To 200: NewColor = 46 ' orange
>> Case 201 To 300: NewColor = 12 ' dark yellow
>> Case 301 To 400: NewColor = 10 ' green
>> Case 401 To 600: NewColor = 3 ' red
>> Case 601 To 1000: NewColor = 20 ' lighter blue
>> End Select
>> Target.Interior.ColorIndex = NewColor
>> End If
>> End Sub
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> Range("F1:F1") = Range("F1:F1").Interior.ColorIndex
>> End Sub
>>
>>
>>
>> On Thu, Aug 25, 2011 at 1:07 PM, Zafar Iqbal wrote:
>>
>>> Dear Excel Experts,
>>>
>>>
>>>
>>> I want graphical presentation of each employee's assignment duration in
>>> days with conditional formatting described in attached sheet.
>>>
>>> These color bars should move forward, backward, increase or decrease
>>> automatically when their duration is changed.
>>>
>>> In Excel - 2003, conditional formatting with only three options is
>>> possible. Here, I have four conditions i.e. Assign-1, 2, 3 and vacation.
>>>
>>> Please help me in preparing this sheet with automatic floating colors.
>>> Thanks in advance.
>>>
>>>
>>>
>>> Regards,
>>>
>>> ZAFAR IQBAL
>>>
>>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ How to attach Excel file here in this post
Dear Group, How to attach Excel file here in this post? I cannot find any button for attaching files here in this page. Please help. Thanks Zafar Iqbal -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Please help to make my project good.
Dear Mr. Fabio Lemos,
I replaced ; with , and got the result. Thanks for this short formula.
Please discard my previous e-mail for sample file. Thanks
Regards,
ZAFAR IQBAL
From: Zafar Iqbal [mailto:ziqba...@gmail.com]
Sent: Sunday, May 15, 2011 10:15 AM
To: 'Fabio Lemos'
Cc: 'excel-macros@googlegroups.com'
Subject: $$Excel-Macros$$ Please help to make my project good.
Dear Mr. Fabio Lemos,
Thanks for your interest.
I tried to apply your below mentioned formula. I am getting error message.
Can you please send me sample file with formula. Thanks a lot for this
kindness.
Regards,
ZAFAR IQBAL
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Fabio Lemos
Sent: Saturday, May 14, 2011 7:03 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Please help to make my project good.
You also could simplify the formula by inserting the following in the E2
cell:
=OFFSET(INDIRECT("'"&$A$2&"'!$A$1";TRUE);$C$21+ROW(E2)-2;0)
And tham just copy to the other
2011/5/13 Zafar Iqbal
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ Please help to make my project good.
Dear Mr. Fabio Lemos,
Thanks for your interest.
I tried to apply your below mentioned formula. I am getting error message.
Can you please send me sample file with formula. Thanks a lot for this
kindness.
Regards,
ZAFAR IQBAL
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Fabio Lemos
Sent: Saturday, May 14, 2011 7:03 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Please help to make my project good.
You also could simplify the formula by inserting the following in the E2
cell:
=OFFSET(INDIRECT("'"&$A$2&"'!$A$1";TRUE);$C$21+ROW(E2)-2;0)
And tham just copy to the other
2011/5/13 Zafar Iqbal
Dear, With due respect to your effort, I tried with a simplified formula and
got the same result. Please find the attached file with both formulas.
Thanks
Regards,
ZAFAR IQBAL
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of §»VIPER«§
Sent: Thursday, May 12, 2011 8:36 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Please help to make my project good.
pfa
On Thu, May 12, 2011 at 12:31 AM, Mahesh parab wrote:
Hi
check whether attach file helps.
On Wed, May 11, 2011 at 3:51 PM, karan 1237 wrote:
Respected Sirs/Mam,
I need your help making my project good. Please find attachment. I will be
very obliged if someone can help me as earlier as possible.
--
Basically I have to do that when I click on drop down list & select another
career i.e. Building and Construction so In sub career list data should
comes from the Building and construction's sheet and If I select another
career i.e. Catering and Hospitality so data should comes from its list
(Catering and Hospitality) and scroll bar should work with the same. I have
16 careers so please tell me how can I do this.
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
$$Excel-Macros$$ maximum value of scroll bar should be linked to max data of individual sheet
Dear All, Is it possible that maximum value of scroll bar should be linked to total number of entries in related data of individual sheet? If A2 is Administration, Business and Of Max value of scroll bar should be 46. If A2 is Building and Construction, Max value of scroll bar should be 63. If A2 is Catering and Hospitality, Max value of scroll bar should be 20. This is avoid Zeros coming in cells when scrolling down beyond 20 or 46 for second & third sheet data. Regards, ZAFAR IQBAL Mobile: 0556 014 035 From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of STDEV(i) Sent: Thursday, May 12, 2011 3:04 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Please help to make my project good. please check the attachment if it helps.. best regards, STDEV(i) note: we need dynamic Max Value of ScrollBar so we use Scroolbar from ActiveX Control, instead of from FORM. A little macro wil work each time you change the "career" On Wed, May 11, 2011 at 5:21 PM, karan 1237 wrote: Respected Sirs/Mam, I need your help making my project good. Please find attachment. I will be very obliged if someone can help me as earlier as possible. -- Basically I have to do that when I click on drop down list & select another career i.e. Building and Construction so In sub career list data should comes from the Building and construction's sheet and If I select another career i.e. Catering and Hospitality so data should comes from its list (Catering and Hospitality) and scroll bar should work with the same. I have 16 careers so please tell me how can I do this. Thnx in Advance ııııllıı ~ ♣ кαяαηη ♣ ~ ııııllıı -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Please help to make my project good.
Dear, With due respect to your effort, I tried with a simplified formula and got the same result. Please the attached file with both formulas. Thanks Regards, ZAFAR IQBAL From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of §»VIPER«§ Sent: Thursday, May 12, 2011 8:36 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Please help to make my project good. pfa On Thu, May 12, 2011 at 12:31 AM, Mahesh parab wrote: Hi check whether attach file helps. On Wed, May 11, 2011 at 3:51 PM, karan 1237 wrote: Respected Sirs/Mam, I need your help making my project good. Please find attachment. I will be very obliged if someone can help me as earlier as possible. -- Basically I have to do that when I click on drop down list & select another career i.e. Building and Construction so In sub career list data should comes from the Building and construction's sheet and If I select another career i.e. Catering and Hospitality so data should comes from its list (Catering and Hospitality) and scroll bar should work with the same. I have 16 careers so please tell me how can I do this. -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Simplified IF with Offset.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ How to change Date format
Excellent formula. Well Done. Regards, ZAFAR IQBAL From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Sixthsense Sent: Friday, May 13, 2011 12:13 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ How to change Date format Hi Prabhu, Try the below formula and format the cell as date. =IF(AND(OR(LEN(TRIM(B2))=5,LEN(TRIM(B2))=6),ISNUMBER(B2)),IF(LEN(TRIM(B2))=5 ,--(--MID(TRIM(B2),2,2)&"-"&--LEFT(TRIM(B2),1)&"-"&--MID(TRIM(B2),4,2)),--(- -MID(TRIM(B2),3,2)&"-"&--LEFT(TRIM(B2),2)&"-"&--MID(TRIM(B2),5,2))),"") Herewith I have attached a sample file for your reference. Hope that helps! --- Sixthsense :) Man of Extreme & Innovative Thoughts :) On Thu, May 12, 2011 at 5:44 PM, Prabhu wrote: Hi friends, Plz help to change date format when downloaded report from 1st of every month to 9th of the month will be like DMMYY . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ Total of sum of Diff. criteria according to date
Dear Mr. Rohan, Please check the attached file (formula in red font), if it fulfills your needs. Regards, ZAFAR IQBAL From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Rohan Young Sent: Tuesday, May 10, 2011 8:48 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Total of sum of Diff. criteria according to date Dear Experts, Daily updation of the status according to skilled & unskilled type labour, I need a total sum of skilled & unskilled if I change the date only. because total sum of these diff. criteria is use in some other different formats. Sample file attached (for better understanding) thanks & regds ROHAN -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Please check.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Macro to make Excel File Expired after few times open & close
Dear Mr. Andre, Thanks for this help. I observed that this macro will not work if the file is closed without SAVE. How to rectify this matter? I request that file should be discarded just after 4 times open independent of Save or No Save. Thanks Regards, Zafar Iqbal 2011/4/27 André D'Avila > Zafar, > > I attached a excel file and readapted for your need. > > Just change in my code the "Sheet" and the "collumn" for a less visible one > as possible. > > You can open and save the file for 4 turns only... > > > Good Luck! > > Best Regards > > André Luiz D´Avila > > Em 27 de abril de 2011 16:20, André D'Avila > escreveu: > > Hey, >> im not an expert but a long time I did something close to what you need... >> >> Each time the file is opened and after the user enable macro security, i >> did a code to write in Plan1 column ZZ line 1 "=now()" >> After save it, the 2 time the user open the file, it writes down the date >> and time in line 2 >> You can make a code to do not open after 4 registers in column zz >> >> I will try to find the file to send you >> >> Best Regards >> >> André Luiz D´Avila >> >> >> 2011/4/27 Zafar Iqbal >> >>> Dear All, >>> >>> Can someone help in making a macro which will make the Excel file expired >>> after few tries of open & close. For example, file can be opned, saved & >>> closed for four times but this file should not open after 5th time to open. >>> Please help. Thanks >>> Regards, >>> ZAFAR IQBAL >>> >>> -- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Macro to make Excel File Expired after few times open & close
Dear All, Can someone help in making a macro which will make the Excel file expired after few tries of open & close. For example, file can be opned, saved & closed for four times but this file should not open after 5th time to open. Please help. Thanks Regards, ZAFAR IQBAL Incharge Planning Olayan Descon Industrial Co, Jubail, KSA Email: ziqba...@gmail.com -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
