AutoFilter Reset

[Johnboy]

I'm trying to reset all autofilters in a workbook. The following macro works if only one sheet in the workbook has autofilters. I get the following error when more then one sheet has autofilters

Run-time error '1004': Method 'ShowAllData' of object '_Worksheet' failed

The folowing code is in "ThisWorkbook"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.AutoFilterMode Then ws.ShowAllData
    Next ws
End Sub

Thanks
Johnboy

 

[JLGWhiz]

I'm trying to reset all autofilters in a workbook. The following macro works if only one sheet in the workbook has autofilters. I get the following error when more then one sheet has autofilters

Run-time error '1004': Method 'ShowAllData' of object '_Worksheet' failed

The folowing code is in "ThisWorkbook"

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.AutoFilterMode Then ws.ShowAllData
    Next ws
End Sub

Thanks
Johnboy

Cannot duplicate the error.

 

[simi_uk]

I've just encountered this same error with the code in Excel 2003.I'm trying to reset the AutoFilters to show All item and reset the Auto Filters on a save event.Any suggestions welcomed.