I can't believe how hard I am making this......I must admitt I'm lost, way
over my head. I have pasted the whole macro into this post so hopefully you
can tell me where I am going wrong.

Sub ListBox1_Click()

Sheets("test Database").Range("A25").Value = ListBox1


For i = 0 To UserForm6.ListBox1.ListCount - 1
If UserForm6.ListBox1.Selected(i) Then

Dim ws As Worksheet

Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set ws = Sheets("Test Database")

Set rng = ws.Range("B26:AD2500")

ws.AutoFilterMode = False

rng.AutoFilter Field:=1, Criteria1:="=" & ws.Range("A25").Value



ws.AutoFilter.Range.Copy


Sheets("Last four").Select

Range("B2500").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, _
skipblanks:=False, Transpose:=False

ws.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With



End If
Next

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("B72:B" & lr4), form6)~~~I don't understand
If mCnt = 4 Then
how form6 will
mCnt = 4
will put in auto-
End If
maticly the Mix type
cnt = 1
I pick from the
For i = lr4 To 72 Step -1
listbox.
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

Range("S14").Select


End Sub

This list box will list all the mix types and I can pick from. This is then
copied over to the bottom of sheets("last four"). Then just the last four
tests are copied up to rows 9-12 on sheets("last four").


"JLGWhiz" wrote:

The variable has to equate to a value in column A rows 71 To the last row for
your type mix. You previously stated that you got this value from Form 6 so
I stuck that in as a place holder. If your Form six is a TextBox then the
variable would be equal to the TextBox value. You didn't explatin what the
Form six was so I assumed it was a control which produced a value equal to a
mix type. You have to have something in the places where Form6 appears in
the code that identifies the mix type you want to select the test results for
or it will not select anything.

"Eric" wrote:

joel, Form 6 is a form for no dupes. When he says to put a variable in
place of form 6 what does that mean?

"Joel" wrote:

Did the code work when you had 101? I looked at your early posting and read
the instructions for you to replace form6 with your input data from form 6.
Make sure you are getting good data in variable form6. try adding a msgbox
to debug the code like the one below. Your problem is probably that your
aren't referrencing the input from form6 properly.


Sub last_four_four()


Dim lr4, lc4, mCnt, cnt As Long


msgbox("form6 = " & form6)

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:

when I use this macro it doesn't work. Nothing happens and I don't get any
error reports. I am at a loss. Here it is....

Sub last_four_four()


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

Where it says form6 I was told to use a variable. I was using a number of
101.

Any help would be appreciated. Thank you

Eric