Run Macro when close spreadsheet

[Guest]

I am trying to create a macro that will will remove all filters using the
data - filters - show all option from a spreadsheet when exiting the
spreadsheet. I have a Macro called Auto_Close. This is ok when there are
filters to remove but not when there are none to remove (i.e the data -
filters - show all option is greyed out when there are no filters). How could
i get around this ?

Thanks

 

[Dave Peterson]

The bad news is that you'd have to save the workbook in that state, too. And
the user may not want any changes that they've made to be saved.

I think I'd use Auto_open() instead of Auto_Close() instead. It'll run each
time the workbook is opened and set it up nicely for that session.

One way is to ignore any error that may occur:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
On Error Resume Next
For Each wks In ThisWorkbook.Worksheets
wks.ShowAllData
Next wks
On Error GoTo 0
End Sub

Another way is to check to see if things need to be reset first:

Option Explicit
Sub auto_open()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.AutoFilterMode Then
If wks.FilterMode Then
wks.ShowAllData
End If
End If
Next wks
End Sub