Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
I am using the following macro to copy the last four rows of information.
Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
Hi Eric, you are getting the error because you are copying the entire row and
then trying to paste it into a shorter space. It won't work that way so the copy range has to be shortened to copensate for the omission of column A in the paste area. Here is a modified code that I think will work for you. If not, post back. With Sheets("last four") lr4 = Cells(Rows.Count, 2).End(xlUp).Row lc4 = Sheets("lastfour").UsedRange.Columns.Count + 1 .Range(.Cells(lr4-3, 2), .Cells(lr4, lc4)).Copy .Range("B9").PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False End With "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
Check the sheet name, I don't think I left a space between last and four.
"Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
that works great thank you. But I do have another questions .... If I only
have one test it shows up in the last row (row A12 from A9 to A12). Is there a was that I can have it populate from the top down with the last (most recent) test on the bottom. ie: 2 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 A12 ie: 4 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 Test #3 date run 1/3 A12 Test #4 date run 1/4 "JLGWhiz" wrote: Check the sheet name, I don't think I left a space between last and four. "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
Your description of the problem is not clear enough for me to give you a good
answer. Since I can't see your worksheet, you need to explain what data is in which columns as well as the rows. For instance, is Test # 1 in the same column as date run 1/1, or are they in the same cell? When you reference row A9, do you mean cell or Range("A9") or just row 9? The more detailed the description of the problem, the better the answer. You are the only one who can see your worksheet. Bear in mind that if you only rearrange data in one column, it will no longer correspond to other data in the original row. That is if you move data from Cell A9 to Cell A12 and everything else remains the same, any dates that are now in Cell A12 that applied to Cells B9 through IV9 are disconnected. So, if you want to rearrange the entire row, then say it like that...entire row. If you want to only move the cell the say either Cell A9 or Range("A9") so we are talking the same language. Think it over and repost with what you are trying to do. I'll check back in a while. "Eric" wrote: that works great thank you. But I do have another questions .... If I only have one test it shows up in the last row (row A12 from A9 to A12). Is there a was that I can have it populate from the top down with the last (most recent) test on the bottom. ie: 2 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 A12 ie: 4 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 Test #3 date run 1/3 A12 Test #4 date run 1/4 "JLGWhiz" wrote: Check the sheet name, I don't think I left a space between last and four. "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
Sorry about that.
Let me try it again. I want to have the earliest test (of the last 4, arranged by date. Some tests will have the same date) to show up on row 9. For instance if I only have one (1) test it will show up in Row 9. If two (2) tests run on two different dates, or the same date, the earliest one would be on row 9 and the next on row 10 and so on. The test results would not be changing columns. This is where the last four tests would be found A B C D 71 4075 5 10.5 1/1/2008 72 4065 5 10.5 1/1/2008 73 4075 8 11.8 2/1/2008 74 4075 7 11.8 3/1/2008 75 4065 3 11.8 3/1/2008 76 4075 9 11.8 3/1/2008 77 78 79 and so on down the page Example for two (2) tests: A B C D 9 4065 5 10.5 1/1/2008 ~~~ Test one (1) of last four 10 4065 3 11.8 3/1/2008 ~~~ Test two (2) of last four 11 No others found in data base 12 Example for four (4) tests: A B C D 9 4075 5 10.5 1/1/2008 ~~~ test one (1) of last four 10 4075 8 11.8 2/1/2008 ~~~ Test two of last four 11 4075 7 11.8 3/1/2008 12 4075 9 11.8 3/1/2008 My no dupes macro looks at column "A" of the main database. I truely hope this helps me explain things better. If not please give me another shot. Thank you again.... Eric Whiz" wrote: Your description of the problem is not clear enough for me to give you a good answer. Since I can't see your worksheet, you need to explain what data is in which columns as well as the rows. For instance, is Test # 1 in the same column as date run 1/1, or are they in the same cell? When you reference row A9, do you mean cell or Range("A9") or just row 9? The more detailed the description of the problem, the better the answer. You are the only one who can see your worksheet. Bear in mind that if you only rearrange data in one column, it will no longer correspond to other data in the original row. That is if you move data from Cell A9 to Cell A12 and everything else remains the same, any dates that are now in Cell A12 that applied to Cells B9 through IV9 are disconnected. So, if you want to rearrange the entire row, then say it like that...entire row. If you want to only move the cell the say either Cell A9 or Range("A9") so we are talking the same language. Think it over and repost with what you are trying to do. I'll check back in a while. "Eric" wrote: that works great thank you. But I do have another questions .... If I only have one test it shows up in the last row (row A12 from A9 to A12). Is there a was that I can have it populate from the top down with the last (most recent) test on the bottom. ie: 2 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 A12 ie: 4 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 Test #3 date run 1/3 A12 Test #4 date run 1/4 "JLGWhiz" wrote: Check the sheet name, I don't think I left a space between last and four. "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
Assuming that the latest date will be the last entry and that will also be
the latest test, This code will paste the last row entered on row 9, next to last entered on row 10, third to last entered on row 11 and fourth to last entered on row 12. If the data in column A for rows 9-12 is not critical to the rows pasted, then this should do what you want. Otherwise you might need to rearrange data in Cells A9 - A12. Sub revs4() With Worksheets(1) 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("A" & i + 9).PasteSpecial Paste:=xlPasteValues Next End With Application.CutCopyMode = False End Sub This will replace the previously provided code. "Eric" wrote: Sorry about that. Let me try it again. I want to have the earliest test (of the last 4, arranged by date. Some tests will have the same date) to show up on row 9. For instance if I only have one (1) test it will show up in Row 9. If two (2) tests run on two different dates, or the same date, the earliest one would be on row 9 and the next on row 10 and so on. The test results would not be changing columns. This is where the last four tests would be found A B C D 71 4075 5 10.5 1/1/2008 72 4065 5 10.5 1/1/2008 73 4075 8 11.8 2/1/2008 74 4075 7 11.8 3/1/2008 75 4065 3 11.8 3/1/2008 76 4075 9 11.8 3/1/2008 77 78 79 and so on down the page Example for two (2) tests: A B C D 9 4065 5 10.5 1/1/2008 ~~~ Test one (1) of last four 10 4065 3 11.8 3/1/2008 ~~~ Test two (2) of last four 11 No others found in data base 12 Example for four (4) tests: A B C D 9 4075 5 10.5 1/1/2008 ~~~ test one (1) of last four 10 4075 8 11.8 2/1/2008 ~~~ Test two of last four 11 4075 7 11.8 3/1/2008 12 4075 9 11.8 3/1/2008 My no dupes macro looks at column "A" of the main database. I truely hope this helps me explain things better. If not please give me another shot. Thank you again.... Eric Whiz" wrote: Your description of the problem is not clear enough for me to give you a good answer. Since I can't see your worksheet, you need to explain what data is in which columns as well as the rows. For instance, is Test # 1 in the same column as date run 1/1, or are they in the same cell? When you reference row A9, do you mean cell or Range("A9") or just row 9? The more detailed the description of the problem, the better the answer. You are the only one who can see your worksheet. Bear in mind that if you only rearrange data in one column, it will no longer correspond to other data in the original row. That is if you move data from Cell A9 to Cell A12 and everything else remains the same, any dates that are now in Cell A12 that applied to Cells B9 through IV9 are disconnected. So, if you want to rearrange the entire row, then say it like that...entire row. If you want to only move the cell the say either Cell A9 or Range("A9") so we are talking the same language. Think it over and repost with what you are trying to do. I'll check back in a while. "Eric" wrote: that works great thank you. But I do have another questions .... If I only have one test it shows up in the last row (row A12 from A9 to A12). Is there a was that I can have it populate from the top down with the last (most recent) test on the bottom. ie: 2 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 A12 ie: 4 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 Test #3 date run 1/3 A12 Test #4 date run 1/4 "JLGWhiz" wrote: Check the sheet name, I don't think I left a space between last and four. "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
P.S. If you need further help on this, make a new posting. I am using the
Google news reader, so I have to go back several panels after a long period of time and I might not find this posting again. Good Luck. "Eric" wrote: Sorry about that. Let me try it again. I want to have the earliest test (of the last 4, arranged by date. Some tests will have the same date) to show up on row 9. For instance if I only have one (1) test it will show up in Row 9. If two (2) tests run on two different dates, or the same date, the earliest one would be on row 9 and the next on row 10 and so on. The test results would not be changing columns. This is where the last four tests would be found A B C D 71 4075 5 10.5 1/1/2008 72 4065 5 10.5 1/1/2008 73 4075 8 11.8 2/1/2008 74 4075 7 11.8 3/1/2008 75 4065 3 11.8 3/1/2008 76 4075 9 11.8 3/1/2008 77 78 79 and so on down the page Example for two (2) tests: A B C D 9 4065 5 10.5 1/1/2008 ~~~ Test one (1) of last four 10 4065 3 11.8 3/1/2008 ~~~ Test two (2) of last four 11 No others found in data base 12 Example for four (4) tests: A B C D 9 4075 5 10.5 1/1/2008 ~~~ test one (1) of last four 10 4075 8 11.8 2/1/2008 ~~~ Test two of last four 11 4075 7 11.8 3/1/2008 12 4075 9 11.8 3/1/2008 My no dupes macro looks at column "A" of the main database. I truely hope this helps me explain things better. If not please give me another shot. Thank you again.... Eric Whiz" wrote: Your description of the problem is not clear enough for me to give you a good answer. Since I can't see your worksheet, you need to explain what data is in which columns as well as the rows. For instance, is Test # 1 in the same column as date run 1/1, or are they in the same cell? When you reference row A9, do you mean cell or Range("A9") or just row 9? The more detailed the description of the problem, the better the answer. You are the only one who can see your worksheet. Bear in mind that if you only rearrange data in one column, it will no longer correspond to other data in the original row. That is if you move data from Cell A9 to Cell A12 and everything else remains the same, any dates that are now in Cell A12 that applied to Cells B9 through IV9 are disconnected. So, if you want to rearrange the entire row, then say it like that...entire row. If you want to only move the cell the say either Cell A9 or Range("A9") so we are talking the same language. Think it over and repost with what you are trying to do. I'll check back in a while. "Eric" wrote: that works great thank you. But I do have another questions .... If I only have one test it shows up in the last row (row A12 from A9 to A12). Is there a was that I can have it populate from the top down with the last (most recent) test on the bottom. ie: 2 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 A12 ie: 4 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 Test #3 date run 1/3 A12 Test #4 date run 1/4 "JLGWhiz" wrote: Check the sheet name, I don't think I left a space between last and four. "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and paste
I noticed an error in the last code. Use this one instead.
Sub revs4() With Worksheets(1) 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 "Eric" wrote: Sorry about that. Let me try it again. I want to have the earliest test (of the last 4, arranged by date. Some tests will have the same date) to show up on row 9. For instance if I only have one (1) test it will show up in Row 9. If two (2) tests run on two different dates, or the same date, the earliest one would be on row 9 and the next on row 10 and so on. The test results would not be changing columns. This is where the last four tests would be found A B C D 71 4075 5 10.5 1/1/2008 72 4065 5 10.5 1/1/2008 73 4075 8 11.8 2/1/2008 74 4075 7 11.8 3/1/2008 75 4065 3 11.8 3/1/2008 76 4075 9 11.8 3/1/2008 77 78 79 and so on down the page Example for two (2) tests: A B C D 9 4065 5 10.5 1/1/2008 ~~~ Test one (1) of last four 10 4065 3 11.8 3/1/2008 ~~~ Test two (2) of last four 11 No others found in data base 12 Example for four (4) tests: A B C D 9 4075 5 10.5 1/1/2008 ~~~ test one (1) of last four 10 4075 8 11.8 2/1/2008 ~~~ Test two of last four 11 4075 7 11.8 3/1/2008 12 4075 9 11.8 3/1/2008 My no dupes macro looks at column "A" of the main database. I truely hope this helps me explain things better. If not please give me another shot. Thank you again.... Eric Whiz" wrote: Your description of the problem is not clear enough for me to give you a good answer. Since I can't see your worksheet, you need to explain what data is in which columns as well as the rows. For instance, is Test # 1 in the same column as date run 1/1, or are they in the same cell? When you reference row A9, do you mean cell or Range("A9") or just row 9? The more detailed the description of the problem, the better the answer. You are the only one who can see your worksheet. Bear in mind that if you only rearrange data in one column, it will no longer correspond to other data in the original row. That is if you move data from Cell A9 to Cell A12 and everything else remains the same, any dates that are now in Cell A12 that applied to Cells B9 through IV9 are disconnected. So, if you want to rearrange the entire row, then say it like that...entire row. If you want to only move the cell the say either Cell A9 or Range("A9") so we are talking the same language. Think it over and repost with what you are trying to do. I'll check back in a while. "Eric" wrote: that works great thank you. But I do have another questions .... If I only have one test it shows up in the last row (row A12 from A9 to A12). Is there a was that I can have it populate from the top down with the last (most recent) test on the bottom. ie: 2 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 A12 ie: 4 tests run row A9 test #1 date run 1/1 A10 Test #2 date run 1/2 A11 Test #3 date run 1/3 A12 Test #4 date run 1/4 "JLGWhiz" wrote: Check the sheet name, I don't think I left a space between last and four. "Eric" wrote: I am using the following macro to copy the last four rows of information. Here is the problem. The information starts in column "B" and I need to copy it to cells("B9") Not cells("A9"). If I change cells("A9") to cells("B9") I get an error, "area size doesn't match" or something to that effect. So instead of copying the entire row I only want to copy from column B to column AD. Any help would be appreciated thank you. Eric With Sheets("last four") lr4 = Cells(Rows.count, 1)(Columns.count, 1).End(xlUp).Row Rows(lr4 - 3 & ":" & lr4).Select End With Selection.Copy Range("A9").Select Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _ skipblanks:=False, Transpose:=False |
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 |