Hi,

Thanks in advance

The following code is set for Creteria1 to = "A" how do I make it for A, B
or C

With Worksheets("Panel Details").Range("A1:IV2500")
.AutoFilter Field:=1, Criteria1:="A"
.SpecialCells(xlCellTypeVisible).Copy _
Worksheets("SS A").Cells(1, 1)
.AutoFilter
End With


Thanks again

Trev

Trev,
Maybe this will help:
http://www.meadinkent.co.uk/xlfilter.htm

Record a macro whilst you do this to generate the code.

NickHK

"Trever B" <(E-Mail Removed)> wrote in message
news:3DCB10D1-3D54-485F-A5C5-(E-Mail Removed)...
> Hi,
>
> Thanks in advance
>
> The following code is set for Creteria1 to = "A" how do I make it for A, B
> or C
>
> With Worksheets("Panel Details").Range("A1:IV2500")
> .AutoFilter Field:=1, Criteria1:="A"
> .SpecialCells(xlCellTypeVisible).Copy _
> Worksheets("SS A").Cells(1, 1)
> .AutoFilter
> End With
>
>
> Thanks again
>
> Trev

Thanks Nick,

Not much help

It does not help me how to input multi creteria,

Have tried:-
1) = "A" and "B" and "C"
2) = "A" or "B" or "C"

Tried recording a macro but still need help on criteria

Don't know what else to try.

Hope someone else can help.

Trev



"NickHK" wrote:

> Trev,
> Maybe this will help:
> http://www.meadinkent.co.uk/xlfilter.htm
>
> Record a macro whilst you do this to generate the code.
>
> NickHK
>
> "Trever B" <(E-Mail Removed)> wrote in message
> news:3DCB10D1-3D54-485F-A5C5-(E-Mail Removed)...
> > Hi,
> >
> > Thanks in advance
> >
> > The following code is set for Creteria1 to = "A" how do I make it for A, B
> > or C
> >
> > With Worksheets("Panel Details").Range("A1:IV2500")
> > .AutoFilter Field:=1, Criteria1:="A"
> > .SpecialCells(xlCellTypeVisible).Copy _
> > Worksheets("SS A").Cells(1, 1)
> > .AutoFilter
> > End With
> >
> >
> > Thanks again
> >
> > Trev
>
>
>

Did you read this section ?
<Quote>
a single item (e.g. 'SMITH') underneath one of the fields
more than one criteria item in a single row - applies an 'AND' rule (e.g.
'SMITH' and 'XN41')
more than one criteria on different rows - applies an 'OR' rule (e.g. 'XN41'
or '<1500')
</Quote>
So you have to create your criteria in a column of
A
B
C

NickHK

"Trever B" <(E-Mail Removed)> wrote in message
news:07DB491A-7444-40EE-96A1-(E-Mail Removed)...
> Thanks Nick,
>
> Not much help
>
> It does not help me how to input multi creteria,
>
> Have tried:-
> 1) = "A" and "B" and "C"
> 2) = "A" or "B" or "C"
>
> Tried recording a macro but still need help on criteria
>
> Don't know what else to try.
>
> Hope someone else can help.
>
> Trev
>
>
>
> "NickHK" wrote:
>
> > Trev,
> > Maybe this will help:
> > http://www.meadinkent.co.uk/xlfilter.htm
> >
> > Record a macro whilst you do this to generate the code.
> >
> > NickHK
> >
> > "Trever B" <(E-Mail Removed)> wrote in message
> > news:3DCB10D1-3D54-485F-A5C5-(E-Mail Removed)...
> > > Hi,
> > >
> > > Thanks in advance
> > >
> > > The following code is set for Creteria1 to = "A" how do I make it for
A, B
> > > or C
> > >
> > > With Worksheets("Panel Details").Range("A1:IV2500")
> > > .AutoFilter Field:=1, Criteria1:="A"
> > > .SpecialCells(xlCellTypeVisible).Copy _
> > > Worksheets("SS A").Cells(1, 1)
> > > .AutoFilter
> > > End With
> > >
> > >
> > > Thanks again
> > >
> > > Trev
> >
> >
> >

Hi
Autofilter only lets you filter on two criteria at most. For 3
criteria you need advanced filter.
I'll assume you are selecting your criteria from a multiselect
listbox. If not, this should give you a start

Private Sub OKButton_Click()
Dim Grouplistdata As New Collection
Dim i As Integer, GroupCount As Integer
Dim Item As Variant
Dim GroupCriteria As Range

Application.ScreenUpdating = False
With RegGroups.GroupList
'Get the data in the GroupList ListBox
For i = 0 To .ListCount - 1
'for each Group in the list
If .Selected(i) Then
Grouplistdata.Add .List(i)
End If
Next i
End With

GroupCount = Grouplistdata.Count
'Remove any filter present
With Worksheets("Panel Details")
On Error Resume Next 'required if Advanced filter used
.ShowAllData
On Error GoTo 0
.AutoFilterMode = False 'Removes drop down arrows
End With
Select Case GroupCount
'Check the user hasn't clicked OK on the ViewList userform without
selecting anything
Case 0
Exit Sub 'nothing happens and form is still visible
'Use AutoFilter if GroupCount is 1 or 2
Case 1
Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
Field:=1, Criteria1:=CStr(Grouplistdata(1))
Case 2
Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
Field:=1, Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr,
Criteria2:=CStr(Grouplistdata(2))
Case Is > 2
'Start by inserting a worksheet and creating a Criteria Range to put
into AdvancedFilter
'addSheet first removes the sheet, so it is always a fresh sheet
AddSheet "WorkSpaceSheet" 'now the active sheet
With ActiveWorkbook.Worksheets("WorkSpaceSheet")
.Visible = False
.Cells(1, 1).Value = "Group" 'same as heading on worksheet
for "A", "B", "C",..
i = 1 'set a counter and put group items below "Group"
For Each Item In Grouplistdata
i = i + 1
.Cells(i, 1).Value = Item
Next Item

Set GroupCriteria = .Cells(1, 1).CurrentRegion
End With 'worksheet
'Filter the list
With Worksheets("Panel Details")
.Activate
.Range("A1:IV2500").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria
End with
End Select
End Sub

regards
Paul

Yes I did read the section but still did not like it and still no joy.

However Paul Robinson seams to have the answer

Thanks anyway

Regards

Trev



"NickHK" wrote:

> Did you read this section ?
> <Quote>
> a single item (e.g. 'SMITH') underneath one of the fields
> more than one criteria item in a single row - applies an 'AND' rule (e.g.
> 'SMITH' and 'XN41')
> more than one criteria on different rows - applies an 'OR' rule (e.g. 'XN41'
> or '<1500')
> </Quote>
> So you have to create your criteria in a column of
> A
> B
> C
>
> NickHK
>
> "Trever B" <(E-Mail Removed)> wrote in message
> news:07DB491A-7444-40EE-96A1-(E-Mail Removed)...
> > Thanks Nick,
> >
> > Not much help
> >
> > It does not help me how to input multi creteria,
> >
> > Have tried:-
> > 1) = "A" and "B" and "C"
> > 2) = "A" or "B" or "C"
> >
> > Tried recording a macro but still need help on criteria
> >
> > Don't know what else to try.
> >
> > Hope someone else can help.
> >
> > Trev
> >
> >
> >
> > "NickHK" wrote:
> >
> > > Trev,
> > > Maybe this will help:
> > > http://www.meadinkent.co.uk/xlfilter.htm
> > >
> > > Record a macro whilst you do this to generate the code.
> > >
> > > NickHK
> > >
> > > "Trever B" <(E-Mail Removed)> wrote in message
> > > news:3DCB10D1-3D54-485F-A5C5-(E-Mail Removed)...
> > > > Hi,
> > > >
> > > > Thanks in advance
> > > >
> > > > The following code is set for Creteria1 to = "A" how do I make it for
> A, B
> > > > or C
> > > >
> > > > With Worksheets("Panel Details").Range("A1:IV2500")
> > > > .AutoFilter Field:=1, Criteria1:="A"
> > > > .SpecialCells(xlCellTypeVisible).Copy _
> > > > Worksheets("SS A").Cells(1, 1)
> > > > .AutoFilter
> > > > End With
> > > >
> > > >
> > > > Thanks again
> > > >
> > > > Trev
> > >
> > >
> > >
>
>
>

Thanks Paul

Works a treat

Trev

"(E-Mail Removed)" wrote:

> Hi
> Autofilter only lets you filter on two criteria at most. For 3
> criteria you need advanced filter.
> I'll assume you are selecting your criteria from a multiselect
> listbox. If not, this should give you a start
>
> Private Sub OKButton_Click()
> Dim Grouplistdata As New Collection
> Dim i As Integer, GroupCount As Integer
> Dim Item As Variant
> Dim GroupCriteria As Range
>
> Application.ScreenUpdating = False
> With RegGroups.GroupList
> 'Get the data in the GroupList ListBox
> For i = 0 To .ListCount - 1
> 'for each Group in the list
> If .Selected(i) Then
> Grouplistdata.Add .List(i)
> End If
> Next i
> End With
>
> GroupCount = Grouplistdata.Count
> 'Remove any filter present
> With Worksheets("Panel Details")
> On Error Resume Next 'required if Advanced filter used
> .ShowAllData
> On Error GoTo 0
> .AutoFilterMode = False 'Removes drop down arrows
> End With
> Select Case GroupCount
> 'Check the user hasn't clicked OK on the ViewList userform without
> selecting anything
> Case 0
> Exit Sub 'nothing happens and form is still visible
> 'Use AutoFilter if GroupCount is 1 or 2
> Case 1
> Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
> Field:=1, Criteria1:=CStr(Grouplistdata(1))
> Case 2
> Worksheets("Panel Details").Range("A1:IV2500").AutoFilter
> Field:=1, Criteria1:=CStr(Grouplistdata(1)), Operator:=xlOr,
> Criteria2:=CStr(Grouplistdata(2))
> Case Is > 2
> 'Start by inserting a worksheet and creating a Criteria Range to put
> into AdvancedFilter
> 'addSheet first removes the sheet, so it is always a fresh sheet
> AddSheet "WorkSpaceSheet" 'now the active sheet
> With ActiveWorkbook.Worksheets("WorkSpaceSheet")
> .Visible = False
> .Cells(1, 1).Value = "Group" 'same as heading on worksheet
> for "A", "B", "C",..
> i = 1 'set a counter and put group items below "Group"
> For Each Item In Grouplistdata
> i = i + 1
> .Cells(i, 1).Value = Item
> Next Item
>
> Set GroupCriteria = .Cells(1, 1).CurrentRegion
> End With 'worksheet
> 'Filter the list
> With Worksheets("Panel Details")
> .Activate
> .Range("A1:IV2500").AdvancedFilter
> Action:=xlFilterInPlace, CriteriaRange:=GroupCriteria
> End with
> End Select
> End Sub
>
> regards
> Paul
>
>

Hi
I forgot to insert the AddSheet macro, so it did well to work!

Public Sub AddSheet(TheSheetName As String)
Dim wsNew As Worksheet 'we will insert a fresh worksheet
With ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
.Worksheets(TheSheetName).Delete
On Error GoTo 0
Application.DisplayAlerts = True
'If worksheet is not there the error in deletion is ignored
'Add a new sheet
Set wsNew = .Worksheets.Add(after:=Worksheets("Panel Details"))
'Makes wsNew the active sheet
wsNew.Name = TheSheetName
End With
Set wsNew = Nothing
End Sub

regards
Paul