How do I create this macro?

Amstrad

New Member
Joined
Feb 17, 2011
Messages
23
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:

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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Code:
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
 
Upvote 0
Try:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top