Applying Macro to Multiple Sheets on Same Workbook

[Amstrad]

Hi

I'm trying to create a macro that will be applied to all worksheets in the same workbook. Each worksheet contains a column called Status. Where the status is -3 or -6, I want to delete the entire row of data.

I tried to create a macro, where I apply a filter to the status column and filter out -6 and -3. I then delete the rows. I then saved this macro and then added it to the following macro, which applies it all worksheets:

Sub Remove6and3AllSheets()
Dim wsheet As Worksheet

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

Next wsheet

End Sub

The problem is, it works fine where -6 and -3 actually occurs in the worksheet. However, some of the worksheets do not contain this staus so I get the following error:

Run-time error '1004'

Autofilter method of Range class failed

When I debug it highlights Selection.AutoFilter:

Selection.AutoFilter
ActiveSheet.Range("$A$1:$O$23").AutoFilter Field:=2, Criteria1:="=-6", _
Operator:=xlOr, Criteria2:="=-3"
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter

Any advice on how I can get this working would be really appreciated. Thanks.

 

[GlennUK]

Is this any use to you:

http://www.mrexcel.com/forum/showthread.php?t=358420

?

 

[brans1982]

This probably isn't the best way to do it but try this...

Selection.AutoFilter
On Error GoTo NoData
ActiveSheet.Range("$A$1:$O$23").AutoFilter Field:=2, Criteria1:="=-6", _
Operator:=xlOr, Criteria2:="=-3"
On Error GoTo 0
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Selection.AutoFilter

Exit Sub

NoData:

MsgBox"There is no data for selected criteria.",vbexclamation,"No Data."