Hi I guess you are getting confused between workbook & sheet. Workbook means a Excel File which has many sheets like sheet 1, sheet 2 & so on.
So when you say for folder 2 there should be 3 sheet created then i guess you mean 3 files, am i right? Now what should be name of 3 new workbook? What should be name of file which will store in folder2 value from I4 i.e 07-AB-001 What should be name of file which will store in folder2 value from I6, i.e 07-EF-001-Y-01 Thanks On Mon, May 5, 2014 at 12:45 AM, prkhan56 <prkha...@gmail.com> wrote: > Dear Vaibhav, > > Sorry to trouble you once again. > > Name1, Name2 etc are NOT sheet Names they are Description Names > > If you read my previous post, each Folder as only ONE Sheet. I need to > paste the Concatenated Ref (in Cell C2) and Description (in Cell C3) on the > sheets present in the Folders where x is found. > > Number of Sheets needs to be created according to the x found in the > Column in that particular Folder only. > > See sample attached. > > Row 4 – Sheet in Folder 1, 2 and Folder 5 should show the Ref and > Description > > Row 6 – Sheet in Folder 2 and Folder 4 should show the Ref and Description > > Row7 – Sheet in Folder 3 should show Ref and Description > > Row 8 – Sheet in Folder 2 should show Ref and Description > > Hope it is clear now. > > Thanks a lot for all the help and efforts. > > Rashid Khan > > On Sunday, May 4, 2014 12:19:35 PM UTC+4, Vabz wrote: > >> Ok >> >> I have not changed method of selecting range. Select Range I4 to down as >> per last macro & macro will check excel file in a particular folder. >> >> If folder is absent macro will stop & throw error. If there is no excel >> file you will get error too. >> >> Macro will check for Sheet Name as per name mentioned in range I. If >> Excel sheet is not there new sheet with the name .ie. Name1 or Name2 etc. >> will be created & data will be pasted there in. Make sure that destination >> cell is not merged else values wont be pasted & you wont get error too. >> >> Cheers!! >> >> >> On Sun, May 4, 2014 at 1:48 AM, prkhan56 <prkh...@gmail.com> wrote: >> >>> Dear Vaibhav, >>> >>> The code is superb but I am not getting the desired result. >>> >>> I deleted the Output sheet which I think was making confusion. >>> >>> All folders are stored in the path C:\Test\Macro >>> >>> Each folder in the above path has one sheet in which the concatenated >>> values of Ref and Names should be pasted respectively in C2 and C3 from the >>> Row if an x is found. I have colored the cells for easy understanding of my >>> requirement (see sample attached) >>> >>> When I run the Macro and I select the data I4 to N10 i.e Folder1 to >>> Folder5 >>> >>> *It should paste the concatenated values in the sheet found in the >>> respective folders viz.* >>> >>> Col J - Folder1 >>> 07-AB-001 in Cell C2 and Name1 in C3 – No sheets to >>> be created as there is only one x in this Column >>> >>> Col K - Folder2>>> three sheets should be created as there are three x >>> in this column following should be result: >>> >>> 07-AB-001 in Cell C2 and Name1 in C3 – first sheet >>> >>> 07-CD-001 in Cell C2 and Name3 in C3 – second sheet >>> >>> 00-FG-002-Z in Cell C2 and Name5 in C3 – third sheet >>> >>> Col L – Folder3>>> 00-EF-001-Y-01 in C2 and Name4 in C3 >>> >>> Col M – Folder4>>> 07-CD-001 in Cell C2 and Name3 in C3 >>> >>> *To summarize my requirement the macro should:* >>> >>> Look for the x in the selected range >>> >>> Go the Folder names in the path C:\Test\Macro if x is found in a Column >>> >>> Create number of sheets as per the x found in a column and paste the >>> concatenated Ref No (in Cell C2) and Names (in Cell C3) from the same row >>> where it finds an x. >>> >>> Sorry for the trouble but I really appreciate your time and help >>> >>> Thanks for everything. >>> >>> Regards >>> >>> Rashid Khan >>> >>> On Saturday, May 3, 2014 5:35:04 PM UTC+4, Vabz wrote: >>> >>>> Remove space from name of folder. >>>> >>>> It should be "Folder5" & not "Folder 5". >>>> >>>> Thx >>>> >>>> >>>> On Sat, May 3, 2014 at 5:14 PM, prkhan56 <prkh...@gmail.com> wrote: >>>> >>>>> Hello Vaibhav >>>>> >>>>> When I run the macro >>>>> >>>>> I get this error: Run time 1004 >>>>> >>>>> Microsoft Excel cannot access the file C:\Test\Macro\Folder 5….*.I >>>>> don’t know how Folder 5 is coming here please* >>>>> >>>>> The following gets highlighted >>>>> >>>>> .SaveAs "C:\Test\Macro\" & FolderName & "\" _ >>>>> >>>>> & c.Value & FileExtStr, _ >>>>> >>>>> FileFormat:=FileFormatNum >>>>> >>>>> Any solution so I can test it further please? >>>>> >>>>> Thanks once again. >>>>> >>>>> Rashid Khan >>>>> >>>>> On Saturday, May 3, 2014 10:13:53 AM UTC+4, Vabz wrote: >>>>> >>>>>> Hi >>>>>> >>>>>> PFA >>>>>> >>>>>> HTC//Cheers >>>>>> >>>>>> >>>>>> On Sat, May 3, 2014 at 2:13 AM, prkhan56 <prkh...@gmail.com> wrote: >>>>>> >>>>>>> Dear Vabz, >>>>>>> >>>>>>> Thanks a lot for your time and effort. >>>>>>> >>>>>>> At present when I run the Macro it creates Folder1, Folder2 etc in >>>>>>> Documents Folder and some Excel files in the same folder. >>>>>>> >>>>>>> But maybe I need to explain my requirements once again …hope I will >>>>>>> be clear this time. >>>>>>> >>>>>>> 1. >>>>>>> >>>>>>> The macro should look for x in J to AX >>>>>>> 2. >>>>>>> >>>>>>> If it finds x then it should go to the Path “C:\Test\Macro” and >>>>>>> no new Folders should be created. >>>>>>> 3. >>>>>>> >>>>>>> The Path “C:\Test\Macro\” has Folder1, Folder2, Folder3 etc >>>>>>> (names are mentioned from J3 to AX3) having an excel file with one >>>>>>> sheet >>>>>>> only (as per sample shown in the output sheet). >>>>>>> 4. >>>>>>> >>>>>>> If there is one x – let’s see the example of Cell J4; then the >>>>>>> macro should fill concatenated values from respective rows A4 which >>>>>>> is >>>>>>> 07-AB-001 and Name1 from I4 in Folder1. >>>>>>> 5. >>>>>>> >>>>>>> If there is more than one x (eg Column K) then 3 sheets should >>>>>>> be created and filled with the concatenated Ref and Names in the >>>>>>> respective >>>>>>> row where x is shown. >>>>>>> 6. >>>>>>> >>>>>>> Column L values in Folder3, Column M values in Folder4 and so >>>>>>> on. If there is no x in the Column then no action required. >>>>>>> 7. >>>>>>> >>>>>>> Only if x is shown in the Column then new sheet would be created >>>>>>> as per the x found and respective Ref and Names pasted. >>>>>>> >>>>>>> Hope it is clear this time. >>>>>>> >>>>>>> Thanks once again for your help >>>>>>> >>>>>>> Regards >>>>>>> >>>>>>> Rashid Khan >>>>>>> >>>>>>> >>>>>>> On Thursday, May 1, 2014 2:02:57 PM UTC+4, Vabz wrote: >>>>>>> >>>>>>>> Hi >>>>>>>> >>>>>>>> Refer this, >>>>>>>> >>>>>>>> when prompted for selecting range you need to select range from I4 >>>>>>>> to down.. >>>>>>>> >>>>>>>> Cheers!! >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Thu, May 1, 2014 at 3:18 PM, Vaibhav Joshi <v...@vabs.in> wrote: >>>>>>>> >>>>>>>>> Hi >>>>>>>>> >>>>>>>>> Pfa. Click to run Macro. Put "\" at the end of folder name. >>>>>>>>> >>>>>>>>> Cheers!! >>>>>>>>> >>>>>>>>> >>>>>>>>> On Thu, May 1, 2014 at 10:35 AM, prkhan56 <prkh...@gmail.com>wrote: >>>>>>>>> >>>>>>>>>> Hello Paul, >>>>>>>>>> I don't how to write a macro...and I request someone to write it >>>>>>>>>> for me please. >>>>>>>>>> >>>>>>>>>> Thanks >>>>>>>>>> Rashid >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> On Wednesday, April 30, 2014 5:21:27 PM UTC+4, Paul Schreiner >>>>>>>>>> wrote: >>>>>>>>>> >>>>>>>>>>> Do you know how to write macros and are asking for help? >>>>>>>>>>> or are you asking for someone to write the macro? >>>>>>>>>>> >>>>>>>>>>> *Paul* >>>>>>>>>>> ----------------------------------------- >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> *“Do all the good you can,By all the means you can,In all the >>>>>>>>>>> ways you can,In all the places you can,At all the times you can,To >>>>>>>>>>> all the >>>>>>>>>>> people you can,As long as ever you can.” - John Wesley * >>>>>>>>>>> ----------------------------------------- >>>>>>>>>>> >>>>>>>>>>> *From:* Rashid Khan <prkh...@gmail.com> >>>>>>>>>>> *To:* excel-macros <excel-...@googlegroups.com> >>>>>>>>>>> *Sent:* Wednesday, April 30, 2014 8:05 AM >>>>>>>>>>> *Subject:* $$Excel-Macros$$ Macro to look for x in Columns and >>>>>>>>>>> paste data in worksheet in various folders >>>>>>>>>>> >>>>>>>>>>> Hello All, >>>>>>>>>>> I am using Excel 2010 and have the following problem. >>>>>>>>>>> >>>>>>>>>>> I need a macro to look for x in Columns J to AX (Sample File >>>>>>>>>>> Attached). >>>>>>>>>>> >>>>>>>>>>> If it finds x then it should go to the Folder “C:\Test\Macro” >>>>>>>>>>> which contains the Folder names shown in J3 to AX3. >>>>>>>>>>> >>>>>>>>>>> It should open the file inside the respective Folders 1, 2, >>>>>>>>>>> 3….etc and paste the values No1 to No5 shown in Cells A3, C3, E3, >>>>>>>>>>> F3 H3 >>>>>>>>>>> (see various possibility shown here). >>>>>>>>>>> >>>>>>>>>>> No1-No2-No3-No4-No5 >>>>>>>>>>> No1-No2-No3 >>>>>>>>>>> No1-No2-No3-No4 >>>>>>>>>>> >>>>>>>>>>> It should be concatenated with a hyphen. If any No is blank >>>>>>>>>>> then it should not be concatenated (See Output Sheet on the sample >>>>>>>>>>> file >>>>>>>>>>> attached) >>>>>>>>>>> >>>>>>>>>>> Thanks in advance >>>>>>>>>>> Rashid >>>>>>>>>>> -- >>>>>>>>>>> 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...@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...@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...@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...@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. > -- 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.
