Autofilter More Than 2 Items

[honkin]

I have a VBA macro which does a number of things, but I have not been able to get it to autofilter for more than 2 things, but I need it to do 4

Here is the macro

Sub Low_Risk_Lays()
'
' Low Risk Lays Macro
' This macro will filter for Remove VDW Rank 1, Class, Distance <=1650, # of Runners <=9, Exclude Brighton, Yarmouth, Windsor & Wolverhampton
'
    With ActiveSheet
        With .Cells(1).CurrentRegion
            .AutoFilter Field:=4, Criteria1:="<=9"
            .AutoFilter Field:=11, Criteria1:="<=1650"
            [B].AutoFilter Field:=8, Criteria1:="<>Brighton", Criteria2:="<>Yarmouth", Operator:=xlAnd[/B]
            .AutoFilter Field:=29, Criteria1:="<>1"
            .HorizontalAlignment = xlCenter
        End With
        .Columns("C:C").EntireColumn.Hidden = True
        .Columns("G:G").EntireColumn.Hidden = True
        .Columns("I:I").EntireColumn.Hidden = True
        .Columns("L:L").EntireColumn.Hidden = True
        .Columns("N:W").EntireColumn.Hidden = True
        .Columns("Y:AB").EntireColumn.Hidden = True
        .Columns("AD:AJ").EntireColumn.Hidden = True
        .Columns("AO:AO").EntireColumn.Hidden = True
        .Columns("AQ:BQ").EntireColumn.Hidden = True
        .Columns("BT:CP").EntireColumn.Hidden = True
        .AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("New Results File.xlsm").Sheets("Low Risk Lays").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

The line which is in bold filters for the words Brighton and Yarmouth, but I actually need it to filter for an additional 2 words; Yarmouth & Wolverhampton

How is this possible at all?

Thanks in advance

 

[honkin]

Hi Fluff

Apologies for the long delay in replying. Been swamped here as domestic tourism finally returns to Thailand.

OK, so I changed my VBA code to reflect your suggestion, but now it falls over on this line of code with a runtime error 424 Object Required message.

.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy

Any thoughts as to what might be causing that, as the code hasn't had any error messages before?

 

[jasonb75]

Your code didn't contain

.AutoFilter.[B]Range[/B].Offset(1).SpecialCells(xlCellTypeVisible).Copy

before, if you're going to use Range instead of CurrentRegion then you need to specify what the Range refers to, for example

.AutoFilter.[B]Range[COLOR=rgb(255, 0, 0)][U]("A1:Z1")[/U][/COLOR][/B].Offset(1).SpecialCells(xlCellTypeVisible).Copy

I've used your method of formatting the code in my post so that you can see where I've changed it
If I follow @Yongle's advice on how to format code then you will see that it is much easier to read

.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy

.AutoFilter.Range("A1:Z1").Offset(1).SpecialCells(xlCellTypeVisible).Copy

 

[Fluff]

Change that line to this

        .Parent.AutoFilter.Range.Offset(1).Copy

 

[honkin]

cheers Fluff
That's nailed it. Works like a charm. Thanks so much for your effort. It is very much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback