I thought I told you about that change in the ListBox1 code, but maybe I
forgot.
At my age, I am lucky to remember to get up in the morning. Anyhow, glad you
got it working. I am not sure what your no dupes is. I assume it is
something you run prior to running the autofilter. If it is, and you want
to automatically run the UserForm6 and last four code when you run the no
dupes, then just put a line in the "no dupes" right before the End Sub like:
UserForm6.Show and you are in business. No dupes will run, call
UserForm6, you click the ListBox1 and "last4" will run. Simple.

Good Luck.

"Eric" wrote:

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