JLWhiz,
It works with one exception.....The listbox1 doesn't do a nodupe procedure.

The problem with the macro was in the listbox1 procedure which had
sheet("sheet1") cells("A25") instead of sheets("test database"). Unfortunely
I have a sheet in the workbook call sheet 1 that I record all changes to the
workbook so I can go back and see what I have done .......

Now the only thing is the nodupes. You are fantastic thank you....

Eric

"Eric" wrote:

Here is the macro. I double checked and it is in module 1
Sub last4()


Dim ws As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim lr4, lc4, mCnt, cnt As Long
lr = Sheets("test Database").Cells(Rows.count, 2).End(xlUp).Row
lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row
Set ws = Worksheets("test Database")
Set rng = ws.Range("B26:AD" & lr)
' UserForm6.Show
myVar4 = Sheets("test Database").Range("A25")
If Sheets("test Database").Range("A25") "" Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
ws.AutoFilterMode = False
cRng = Sheets("test Database").Range("A25").Value
rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False
ws.AutoFilter.Range.Copy
Sheets("last four").Range("B" & lr4 + 1).PasteSpecial Paste:=xlValues
ws.AutoFilterMode = False
Application.CutCopyMode = False
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
With Sheets("last four")
lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row
lc4 = Sheets("last four").UsedRange.Columns.count + 1
mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4)
Range("A9:Z12").ClearContents
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If mCnt = 4 Then
mCnt = 4
End If
cnt = 1
For i = lr4 To 72 Step -1
If .Cells(i, 2) = myVar4 Then
If cnt <= 4 Then
Select Case mCnt
Case Is = 1
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B9").PasteSpecial Paste:=xlPasteValues
Case Is = 2
If x = "" Then x = 10
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B" & x).PasteSpecial Paste:=xlPasteValues
x = x - 1
Case Is = 3
If x = "" Then x = 11
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B" & x).PasteSpecial Paste:=xlPasteValues
x = x - 1
Case Is = 4
If x = "" Then x = 12
.Range(.Cells(i, 2), .Cells(i, lc4)).Copy
.Range("B" & x).PasteSpecial Paste:=xlPasteValues
x = x - 1
End Select
cnt = cnt + 1
End If
End If
Next
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End With
Application.CutCopyMode = False
End Sub

Eric

"Eric" wrote:

yes I did mean module 1 sorry about that....

Eric


"JLGWhiz" wrote:

I assume you mean Module 1 below.

"Eric" wrote:

JLGWhiz, I have copied everything this is how it looks....

Macro 1
Start last4() next
last4

Userform6
Userform_Initialize() Next
Listbox1_click()

1) I get the listbox and can pick a mix type then I recieve an error
"Autofilter Method or Range class failed".

2) In this list box can we do the no dupes so that I don't have a mix type
for each test. I only want to see the different mix types not all of them.

3) I will need to delete cells ("B72:AD700") on sheets("last four") after
it posts up on to rows 9 through 12 on sheets("last four"). I figure I can
tack this on to the Last4 macro, is this correct?

4) At the end of your last posting you place New subject. Clearing content
from Range("A9:AD12").clearcontents. Did you think I wanted to clear this?
I do not want this cleared until the next time I need to run this macro.

Eric