Reset autofilter for entire workbook

[paulstan]

The following will reset any auto-filters on all worksheets upon opening the workbook. It won't remove the auto-filters, just reset them.

There are, however, 2 small problems:

1. If I have any worksheet in the workbook (ie data lists) which have no auto-filters, an error is generated:

Runtime error '1004': AutoFilter method of range class failed.

It stops at line

.AutoFilter Fields:=1

Is there a way to get this macro to not recognise sheets which don't have an auto-filter?

2. It won't work on a password-protected sheet either!

Public Sub Workbook_Open()
Dim AW As String
AW = ActiveSheet.Name
Application.ScreenUpdating = False
For WSB = 2 To Worksheets.Count
 Sheets(WSB).Select
 With Selection
  .AutoFilter Field:=1
  .AutoFilter Field:=2
  .AutoFilter Field:=3
  .AutoFilter Field:=4
  .AutoFilter Field:=5
  .AutoFilter Field:=6
  .AutoFilter Field:=7
  .AutoFilter Field:=8
  .AutoFilter Field:=9
  .AutoFilter Field:=10
  .AutoFilter Field:=11
  .AutoFilter Field:=12
 End With
Next WSB
Sheets(AW).Select
Application.ScreenUpdating = True
End Sub

Thanks for looking

Paul S

 

[JackDanIce]

Try:

On Error Resume Next
For WSB = 2 To Worksheets.Count
 Sheets(WSB).Select
 With Selection
    .Unprotect password:="your password"
    If .AutoFilterMode Then .AutoFilterMode = False

 

[Airfix9]

1. use On Error Resume Next before the code and On Error Goto 0 after it. That will jump any errors

2.

Are they all protected using the same password? If so, how about this?

Public Sub Workbook_Open()
Dim AW As String, blnProtect as boolean
AW = ActiveSheet.Name
Application.ScreenUpdating = False
For WSB = 2 To Worksheets.Count
 Sheets(WSB).Select
if sheets(WSB).ProtectContents = true then
blnprotect = true
sheets(WSB).unprotect "password"
else
blnprotect = false
end if

 With Selection
  .AutoFilter Field:=1
  .AutoFilter Field:=2
  .AutoFilter Field:=3
  .AutoFilter Field:=4
  .AutoFilter Field:=5
  .AutoFilter Field:=6
  .AutoFilter Field:=7
  .AutoFilter Field:=8
  .AutoFilter Field:=9
  .AutoFilter Field:=10
  .AutoFilter Field:=11
  .AutoFilter Field:=12
 End With
if blnprotect = true then sheets(WSB).protect "password"
Next WSB
Sheets(AW).Select
Application.ScreenUpdating = True
End Sub

If they all have different passwords, you'd need to write a select case into the code to work out what the password is.

If you are putting passwords into code, remember to password protect your code to stop users from seeing your passwords!

Hope that helps.