Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
JGLWhiz or anyone else.....
I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
Hi Eric, the only thing that I can see in the code you posted is that you
left the worksheet name off. It should be Worksheets("last four") if my memory serves me right, and sometimes it don't. <g Are you getting any error message or does it just stop? "Eric" wrote: JGLWhiz or anyone else..... I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
JLGWhiz,
Good afternoon, it is just stopping and highlighting the line Set a. I did put the name of the sheet in and yes you are correct...last four was the name. Also, I didn't know if you would get this so I tried to explain a little better what I am trying to accomplish. I hope it made a bit more sense. Any sugestions would be grateful. Thank you Eric "JLGWhiz" wrote: Hi Eric, the only thing that I can see in the code you posted is that you left the worksheet name off. It should be Worksheets("last four") if my memory serves me right, and sometimes it don't. <g Are you getting any error message or does it just stop? "Eric" wrote: JGLWhiz or anyone else..... I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
This could take a while. The code will probably have to use a case statement
against the number of tests. I am wondering how I am going to know how many tests there are. Do they begin on a certain row so that I could say: If Range("B", StartRow & ":B" & lr4).Rows.Count < 4 Then 'Use the case statement Else 'Use the code provided End If The examle you gave shows Row 71 as the starting row. Will this always be the starting row? "Eric" wrote: JLGWhiz, Good afternoon, it is just stopping and highlighting the line Set a. I did put the name of the sheet in and yes you are correct...last four was the name. Also, I didn't know if you would get this so I tried to explain a little better what I am trying to accomplish. I hope it made a bit more sense. Any sugestions would be grateful. Thank you Eric "JLGWhiz" wrote: Hi Eric, the only thing that I can see in the code you posted is that you left the worksheet name off. It should be Worksheets("last four") if my memory serves me right, and sometimes it don't. <g Are you getting any error message or does it just stop? "Eric" wrote: JGLWhiz or anyone else..... I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
1) Don't copy column A to row 9.....
2) No to copy column A from row 71 to row 9 3) I am using a no dupe then a form asking what mix type to use. I was going to paste the macro you figure out into the form so that it will run when the mix type is selected. The form I am using is form 6. If you would rather make something up and us that ....Fine. I just want this thing to work and work correctly. Your the smart one here so I'll let you make the decisions....... You are really earning your keep working with me......aren't you sorry you took this one. I really do appreciate everything you are doing for me......Thanks a million.... Eric "JLGWhiz" wrote: 1.If I recall correctly, you said you did not want to copy column A from the lower part of the sheet to row nine, yet you are illustrating that column A is to be copied, Can you clarify that part. 2.If you mean copy including column A but paste beginning in column B on row 9 then, your illustration is misleading. I need to know which is which. 3.And, how will VBA know which particular mix type you are looking for. I can include an input box for the user to enter the mix type to copy, if needed. Let me know. 4.Sorry if I am slow to catch on but as I try to write the code, these questions pop up. If we can get these details worked out, I think I understand the rest. "Eric" wrote: Ugggggg....What did you say? 1) The last four or less tests of the same mix type. 2) I need the earliest test on Row 9 and the latest tests below that. (No reversing) IE: Tests run on 1/3 will be on row 9....Tests run on 2/1 will be on row 10.....3/1 will be on row 11 etc...... "JLGWhiz" wrote: Ok, we are getting there. One more clarification. Are you trying to get the last four or less tests of the same mix type, or the last four or less tests of any combination of mix types. If it is any combination, I think the code will be fairly easy, but if you want to break it out by a specific mix type, it can get hairy. Remember you want to reverse the order that they are listed beginning in row 71 so the latest test shows on line 9. That was easy enough with an array based on four rows, but now we are dealing with from 1 to four rows (assuming that if there are only 4 or less that they will lie on rows 71 - 75) which number has to be determined first, then how to control the copy and paste action so that the later date is on top of the earlier date. If the mix type has to be weeded out, it will require another manipultion to isolate those items. So before I start I want to be sure exactly what we are trying to identify and move. "Eric" wrote: let me try and help you with some information..... 1) All the information for each test is on sheets("test database"). Each row represents one (1) test starting at column "A" over to column "AG". I am copying only columns A through AD to sheets("last four"). The tests will go from row 27 to 2500. 2) Each mix type (ie:4050, 4051,etc.) is listed in column B on the sheets("test Database"). I use a no dupes to pick which mix type I need. They are then posted over onto the sheets("last four") starting at row 71 3) On sheets("Test Database") the mix types are counted Range("BD27:BD54"). Cell ("BD56") sums up Range ("BD27:BD54"). I hope this helps you out. "JLGWhiz" wrote: This could take a while. The code will probably have to use a case statement against the number of tests. I am wondering how I am going to know how many tests there are. Do they begin on a certain row so that I could say: If Range("B", StartRow & ":B" & lr4).Rows.Count < 4 Then 'Use the case statement Else 'Use the code provided End If The examle you gave shows Row 71 as the starting row. Will this always be the starting row? "Eric" wrote: JLGWhiz, Good afternoon, it is just stopping and highlighting the line Set a. I did put the name of the sheet in and yes you are correct...last four was the name. Also, I didn't know if you would get this so I tried to explain a little better what I am trying to accomplish. I hope it made a bit more sense. Any sugestions would be grateful. Thank you Eric "JLGWhiz" wrote: Hi Eric, the only thing that I can see in the code you posted is that you left the worksheet name off. It should be Worksheets("last four") if my memory serves me right, and sometimes it don't. <g Are you getting any error message or does it just stop? "Eric" wrote: JGLWhiz or anyone else..... I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
Thow out everything I gave you before. This is a
completer replacement. Where you see Form6 you will need to substitute a variable for the mix type that you want to check. It took a while to get my head straight but I think this is what you wanted. One of the Pros could make it look prettier but it won't work any better. If there is a problem with this, it will be better to make a new posting in case I happen not to log on. Just post this code and describe the problem. Sub lst4mix() Dim lr4, lc4, mCnt, cnt As long With Worksheets("last four") lr4 = .Cells(Rows.Count, 2).End(xlUp).Row lc4 = .UsedRange.Columns.Count + 1 mCnt = Application.CountIf(.Range("A71:A" & lr4), Form6) If mCnt = 4 Then mCnt = 4 End If cnt = 1 For i = lr4 To 71 Step -1 If .Cells(i, 1) = Form6 Then If cnt <= 4 Then Select Case mCnt Case Is = 1 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9") Case Is = 2 If x = "" Then x = 10 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x) x = x - 1 Case Is = 3 If x = "" Then x = 11 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x) x = x - 1 Case Is = 4 If x = "" Then x = 12 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x) x = x - 1 End Select cnt = cnt + 1 End If End If Next End With End Sub "Eric" wrote: 1) Don't copy column A to row 9..... 2) No to copy column A from row 71 to row 9 3) I am using a no dupe then a form asking what mix type to use. I was going to paste the macro you figure out into the form so that it will run when the mix type is selected. The form I am using is form 6. If you would rather make something up and us that ....Fine. I just want this thing to work and work correctly. Your the smart one here so I'll let you make the decisions....... You are really earning your keep working with me......aren't you sorry you took this one. I really do appreciate everything you are doing for me......Thanks a million.... Eric "JLGWhiz" wrote: 1.If I recall correctly, you said you did not want to copy column A from the lower part of the sheet to row nine, yet you are illustrating that column A is to be copied, Can you clarify that part. 2.If you mean copy including column A but paste beginning in column B on row 9 then, your illustration is misleading. I need to know which is which. 3.And, how will VBA know which particular mix type you are looking for. I can include an input box for the user to enter the mix type to copy, if needed. Let me know. 4.Sorry if I am slow to catch on but as I try to write the code, these questions pop up. If we can get these details worked out, I think I understand the rest. "Eric" wrote: Ugggggg....What did you say? 1) The last four or less tests of the same mix type. 2) I need the earliest test on Row 9 and the latest tests below that. (No reversing) IE: Tests run on 1/3 will be on row 9....Tests run on 2/1 will be on row 10.....3/1 will be on row 11 etc...... "JLGWhiz" wrote: Ok, we are getting there. One more clarification. Are you trying to get the last four or less tests of the same mix type, or the last four or less tests of any combination of mix types. If it is any combination, I think the code will be fairly easy, but if you want to break it out by a specific mix type, it can get hairy. Remember you want to reverse the order that they are listed beginning in row 71 so the latest test shows on line 9. That was easy enough with an array based on four rows, but now we are dealing with from 1 to four rows (assuming that if there are only 4 or less that they will lie on rows 71 - 75) which number has to be determined first, then how to control the copy and paste action so that the later date is on top of the earlier date. If the mix type has to be weeded out, it will require another manipultion to isolate those items. So before I start I want to be sure exactly what we are trying to identify and move. "Eric" wrote: let me try and help you with some information..... 1) All the information for each test is on sheets("test database"). Each row represents one (1) test starting at column "A" over to column "AG". I am copying only columns A through AD to sheets("last four"). The tests will go from row 27 to 2500. 2) Each mix type (ie:4050, 4051,etc.) is listed in column B on the sheets("test Database"). I use a no dupes to pick which mix type I need. They are then posted over onto the sheets("last four") starting at row 71 3) On sheets("Test Database") the mix types are counted Range("BD27:BD54"). Cell ("BD56") sums up Range ("BD27:BD54"). I hope this helps you out. "JLGWhiz" wrote: This could take a while. The code will probably have to use a case statement against the number of tests. I am wondering how I am going to know how many tests there are. Do they begin on a certain row so that I could say: If Range("B", StartRow & ":B" & lr4).Rows.Count < 4 Then 'Use the case statement Else 'Use the code provided End If The examle you gave shows Row 71 as the starting row. Will this always be the starting row? "Eric" wrote: JLGWhiz, Good afternoon, it is just stopping and highlighting the line Set a. I did put the name of the sheet in and yes you are correct...last four was the name. Also, I didn't know if you would get this so I tried to explain a little better what I am trying to accomplish. I hope it made a bit more sense. Any sugestions would be grateful. Thank you Eric "JLGWhiz" wrote: Hi Eric, the only thing that I can see in the code you posted is that you left the worksheet name off. It should be Worksheets("last four") if my memory serves me right, and sometimes it don't. <g Are you getting any error message or does it just stop? "Eric" wrote: JGLWhiz or anyone else..... I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste
1. I was trying to make it so the code I wrote could use the
list box value from your code to get the tests you want to move to rows 9 - 12 but I couldn't get the autofilter to work in the code you are using to move the data from the other sheet to last four. If yours is working then you can just add this. 2. I added a line to clear the contents of A9:AD12 before pasting the new data into them. That should leave only the ones you want to see for the current session. 3. I could not duplicate your error when the number of tests exceed four. Maybe if you copy the code from here and paste it instead of trying to type it, it would prevent errors. Sub lst4mix() Form6 = InputBox("Enter Mix Type") With Worksheets("last four") .Range("A9:AD12").ClearContents lr4 = .Cells(Rows.Count, 2).End(xlUp).Row lc4 = .UsedRange.Columns.Count + 1 mCnt = Application.CountIf(Range("A72:A" & lr4), Form6) If mCnt = 4 Then mCnt = 4 End If cnt = 1 For i = lr4 To 24 Step -1 If .Cells(i, 2) = Form6 Then If cnt <= 4 Then Select Case mCnt Case Is = 1 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy Range("B9") Case Is = 2 If x = "" Then x = 10 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x) x = x - 1 Case Is = 3 If x = "" Then x = 11 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy Range("B" & x) x = x - 1 Case Is = 4 If x = "" Then x = 12 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy Range("B" & x) x = x - 1 End Select cnt = cnt + 1 End If End If Next End With End Sub "Eric" wrote: 1) Don't copy column A to row 9..... 2) No to copy column A from row 71 to row 9 3) I am using a no dupe then a form asking what mix type to use. I was going to paste the macro you figure out into the form so that it will run when the mix type is selected. The form I am using is form 6. If you would rather make something up and us that ....Fine. I just want this thing to work and work correctly. Your the smart one here so I'll let you make the decisions....... You are really earning your keep working with me......aren't you sorry you took this one. I really do appreciate everything you are doing for me......Thanks a million.... Eric "JLGWhiz" wrote: 1.If I recall correctly, you said you did not want to copy column A from the lower part of the sheet to row nine, yet you are illustrating that column A is to be copied, Can you clarify that part. 2.If you mean copy including column A but paste beginning in column B on row 9 then, your illustration is misleading. I need to know which is which. 3.And, how will VBA know which particular mix type you are looking for. I can include an input box for the user to enter the mix type to copy, if needed. Let me know. 4.Sorry if I am slow to catch on but as I try to write the code, these questions pop up. If we can get these details worked out, I think I understand the rest. "Eric" wrote: Ugggggg....What did you say? 1) The last four or less tests of the same mix type. 2) I need the earliest test on Row 9 and the latest tests below that. (No reversing) IE: Tests run on 1/3 will be on row 9....Tests run on 2/1 will be on row 10.....3/1 will be on row 11 etc...... "JLGWhiz" wrote: Ok, we are getting there. One more clarification. Are you trying to get the last four or less tests of the same mix type, or the last four or less tests of any combination of mix types. If it is any combination, I think the code will be fairly easy, but if you want to break it out by a specific mix type, it can get hairy. Remember you want to reverse the order that they are listed beginning in row 71 so the latest test shows on line 9. That was easy enough with an array based on four rows, but now we are dealing with from 1 to four rows (assuming that if there are only 4 or less that they will lie on rows 71 - 75) which number has to be determined first, then how to control the copy and paste action so that the later date is on top of the earlier date. If the mix type has to be weeded out, it will require another manipultion to isolate those items. So before I start I want to be sure exactly what we are trying to identify and move. "Eric" wrote: let me try and help you with some information..... 1) All the information for each test is on sheets("test database"). Each row represents one (1) test starting at column "A" over to column "AG". I am copying only columns A through AD to sheets("last four"). The tests will go from row 27 to 2500. 2) Each mix type (ie:4050, 4051,etc.) is listed in column B on the sheets("test Database"). I use a no dupes to pick which mix type I need. They are then posted over onto the sheets("last four") starting at row 71 3) On sheets("Test Database") the mix types are counted Range("BD27:BD54"). Cell ("BD56") sums up Range ("BD27:BD54"). I hope this helps you out. "JLGWhiz" wrote: This could take a while. The code will probably have to use a case statement against the number of tests. I am wondering how I am going to know how many tests there are. Do they begin on a certain row so that I could say: If Range("B", StartRow & ":B" & lr4).Rows.Count < 4 Then 'Use the case statement Else 'Use the code provided End If The examle you gave shows Row 71 as the starting row. Will this always be the starting row? "Eric" wrote: JLGWhiz, Good afternoon, it is just stopping and highlighting the line Set a. I did put the name of the sheet in and yes you are correct...last four was the name. Also, I didn't know if you would get this so I tried to explain a little better what I am trying to accomplish. I hope it made a bit more sense. Any sugestions would be grateful. Thank you Eric "JLGWhiz" wrote: Hi Eric, the only thing that I can see in the code you posted is that you left the worksheet name off. It should be Worksheets("last four") if my memory serves me right, and sometimes it don't. <g Are you getting any error message or does it just stop? "Eric" wrote: JGLWhiz or anyone else..... I am running the following macro and it is stopping on Set a. I don't know what is going on may be you all can help/ With Worksheets lr4=.Cells(Rows.Count, 2).End(xlUp).Row lc4=.UsedRange.Columns.Count +1 Set a = .Range(.Cells(lr4,2),.Cells(lr4, lc4)) Set b = .Range(.Cells(lr4 -1, 2),.Cells(lr4 - 1, lc4)) Set c = .Range(.Cells(lr4 -2, 2),.Cells(lr4 -2, lc4)) Set d = .Range(.Cells(lr4 -3,2),.Cells(lr4 -3, lc4)) arr4=Array(a,b,c,d) for i =0 to 3 arr4(i).copy .Range("B" & i +9).PasteSpecial Paste:=xlPasteValues Next End With Application.cutcopyMode = False End Sub What I am trying to do is to copy and paste the last four (4) tests. If there is only one (1) test then then it should post on row 9. If there are two (2) tests then tests one (1) should post on row 9 and test two (2) on row 10 and so on Example Data Set......... a b c d 71 4056 1/3/08 5 7 72 4056 1/3/08 5 8 73 4058 1/5/08 3 4 74 4056 1/6/08 2 6 75 4058 1/7/08 3 5 76 4056 1/9/08 6 7 77 4056 1/9/08 7 9 Example #1 Post last four (4) tests for 4056 a b c d 9 4056 1/3/08 5 7 ~~ This is test one (1) 10 4056 1/3/08 5 8 ~~ This is test two (2) 11 4056 1/6/08 2 6 ~~ This is test three from data set for 4056 12 ~~ This is blank because there are only three (3) tests for 4056 Example #2 Post last four (4) tests for 4058 a b c d 9 4058 1/5/08 3 4 ~~ Test one from data set for 4058 10 4058 1/7/08 3 5 ~~ Test two from data set 11 ~~ 11 and 12 left blank because there isn't 12 four tests (only two) Example #3 Post last four (4) for 4058 For this there are five (5) tests for 4058 so test one (1) will be dropped. a b c d 9 4056 1/3/08 5 8 ~~ from data set row 72 (71 was dropped) 10 4056 1/6/08 2 6 ~~ row 74 11 4056 1/9/08 6 7 ~~ row 76 12 4056 1/9/08 7 9 ~~ row 77 Row 71 from data set was dropped because there were a total of five (5) tests for 4056. Since we only need the last four (4) I dropped the first one and went with the last four (4). I hope this helps.......Any help would be greatly appreciated. Eric |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste specific data
I am trying to copy some specific data from one sheet ( the sequence may
change everytime) and paste it to another sheet. Like, i need to copy only those rows which have some employee data in them, for example emp no. in col 3. Can anyone help me with this? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy paste specific data
Have a look at
datafilterautofilterfilter as desiredcopy filtered data -- Don Guillett Microsoft MVP Excel SalesAid Software "SBM" wrote in message ... I am trying to copy some specific data from one sheet ( the sequence may change everytime) and paste it to another sheet. Like, i need to copy only those rows which have some employee data in them, for example emp no. in col 3. Can anyone help me with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |