Hello again kind folks.Ive ask this question before but I still dont have the answer that works.I've used a helper column(K) to help me. I need something that looks to column K,its looking for one of three conditions.First,if blank do nothing.Second if a number 1 is present copy cells A:J to workbook named "WIP".Third,if the number 2 is present copy A:J to workbook named "Finished Jobs".CFW#1.xlsm
Would the workbooks WIP & Finished jobs be open at the time of running the macro? Or are you talking about worksheets?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Well I would think they would NOT be open .I was hoping to have to separate workbooks.But its not absolutely necessary.Thanks patel I'll give that a try.
Last edited by Lou54; 08-17-2012 at 03:21 PM.
this macro copy to worksheets "WIP" and "Finished Jobs"
Sub a() Set sh1 = Sheets(1) Set shWIP = Sheets("WIP") Set shFIN = Sheets("Finished Jobs") LR = sh1.Range("A" & Rows.Count).End(xlUp).Row WIPstartrow = 2 FINstartrow = 2 With sh1 For j = 2 To LR If .Cells(j, 11).Value = 1 Then .Range(.Cells(j, 1), .Cells(j, 10)).Copy shWIP.Cells(WIPstartrow, 1) WIPstartrow = WIPstartrow + 1 End If If .Cells(j, 11).Value = 2 Then .Range(.Cells(j, 1), .Cells(j, 10)).Copy shFIN.Cells(FINstartrow, 1) FINstartrow = FINstartrow + 1 End If Next End With End Sub
Patel I get an error message.
Runtime error"9"
Subscript out of range
I thought maybe its because i dont have the other two sheets named but I tried to name it and it still gives me the same error.Thanks greatly for all the help....Lou
Subscript out of range error is always associated with the sheet name. Please check that the sheet names match with the code with regard to spelling and case.
Thanks patel45 and arlu after I changed the names of the worksheets that code worked great.Thanks for your kind help.....Lou
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
patel45 Im not sure this is allowed but after marking this solved I got to wondering if its possible for you to go over the code you've written.Such as how I could alter it to look in other books and how I might change the code to put what it finds in another workbook instead of a different sheet in the same workbook.Things like were and how I could alter it to look in another place for this information and how to direct it to another location.Things like that.I appeciate greatly all your help.....Lou
Last edited by Lou54; 08-22-2012 at 08:22 AM.
Sub a() Set sh1 = Sheets(1) Set shWIP = Sheets("WIP") Set shFIN = Sheets("Finished Jobs") LR = sh1.Range("A" & Rows.Count).End(xlUp).Row WIPstartrow = 2 FINstartrow = 2 With sh1 For j = 2 To LR If .Cells(j, 11).Value = 1 Then .Range(.Cells(j, 1), .Cells(j, 10)).Copy shWIP.Cells(WIPstartrow, 1) WIPstartrow = WIPstartrow + 1 End If If .Cells(j, 11).Value = 2 Then .Range(.Cells(j, 1), .Cells(j, 10)).Copy shFIN.Cells(FINstartrow, 1) FINstartrow = FINstartrow + 1 End If Next End With myPath = "D:\DATA\test\" arr = Array("WIP", "Finished Jobs") For j = 0 To 1 Filename = arr(j) Sheets(Filename).Copy ActiveWorkbook.SaveAs Filename:=myPath & Filename & ".xlsx", CreateBackup:=False ActiveWorkbook.Close Next End Sub
Hi Patel45 and thanks again for your help.When I tired to run the second formula I get a "Run Time Error 1004"
what do you mean for the second formula ? be more precise.
did you change the line: myPath = "D:\DATA\test\" ?
Last edited by patel45; 08-23-2012 at 02:11 AM.
If solved remember to mark Thread as solved
Im sorry, but no I havent changed anything.I meant the second set of code you put on this thread is all.Thanks for your help....Lou
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Lou54 
Register To Reply
Bookmarks