Copy and Paste

[Eric]

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

 

[JLGWhiz]

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]

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]

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]

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]

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

 

[JLGWhiz]

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

 

[SBM]

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?

 

[Don Guillett]

Have a look at
data>filter>autofilter>filter as desired>copy filtered data