How do I create this macro?

[Amstrad]

In a workbook I have multiple worksheets. In each worksheet there is column called status. I want to create a macro that will filter each worksheet so it only returns rows where status 404 or 12007 are shown. If there is no status 404 or 12007 within the worksheet then the worksheet will just be left blank.

I then want to create a second macro that will perform this task on all the sheets in the workbook at once by using something like the following code:

Sub Status404and12007FilterAll()
Dim wsheet As Worksheet

For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Status404and12007Filter

Next wsheet

End Sub

I tried to create a macro using a filter but the problem is that it does not work when the worksheet does not contain contain a 404 or 12007.

Any help on what I need to do to create this macro would be greatly appreciated.

 

[Andrew Poulsom]

Please post the macro you recorded when AutoFiltering for 404 or 12007.

 

[Amstrad]

Sub Status404and12007Filter()
'
' Status404and12007Filter Macro
'

'
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$O$48").AutoFilter Field:=2, Criteria1:="=404", _
        Operator:=xlOr, Criteria2:="=12007"
    Range("A1:O48").Select
    Range("B1").Activate
End Sub

The other issue I had with this macro is that although it filters the 404 and 12007 from the list, I'm not sure of any way of deleteing the hidden rows so that I'm just left with the filtered data. I don't need the original data, but when I swithch the filter off it just reverts back to how it was before.

Thanks for your help

 

[Andrew Poulsom]

Try:

Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$O$48").AutoFilter Field:=2, Criteria1:="=404", _
        Operator:=xlOr, Criteria2:="=12007"
    Range("A1:O48").Select
    Range("B1").Activate