Copy and paste

[Eric]

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

 

[JLGWhiz]

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

 

[JLGWhiz]

Check the sheet name, I don't think I left a space between last and four.

 

[Eric]

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]

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]

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

 

[JLGWhiz]

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.

 

[JLGWhiz]

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.

 

[JLGWhiz]

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