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 |
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 |