1

I'd like to reset all of my filters when I save a workbook. Currently I have:

Sub ResetFilters()
    On Error Resume Next

    ActiveSheet.ShowAllData
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)


         Dim ws As Worksheet

         For Each ws In ActiveWorkbook.Worksheets

          Call ResetFilters

         Next


    Cancel = False
End Sub

The idea is that when I save, I remove all filters. It isn't working for some reason - does anyone have any idea why?

Edit: more googling found the answer : Excel VBA - Run macro before save

Tl;dr: Always google with the keyword stackoverflow in it - it helps dramatically

0m3r
  • 12,286
  • 15
  • 35
  • 71
Selkie
  • 1,215
  • 1
  • 17
  • 34
  • first of all, do not use On Error Resume Next, this line basically bypass errors that might cause troubles. Also, I would use something like 'Cncl' instead of "Cancel" as a variable to avoid accidentally using a keywords. Change these 2 and see what the program tells you. – Alex May 05 '17 at 15:41
  • I got it - it was the location where I was saving the module. If you can find a better way to remove filters, I'd be happy to hear it - I have yet to find a better method – Selkie May 05 '17 at 15:44
  • I think you're going to have an issue with your use of ActiveSheet. You should probably pass ws to ResetFilters, and use ws instead of ActiveSheet. – Rich Holton May 05 '17 at 15:48
  • Rich - I edited my initial question - I was placing the macro in the wrong module – Selkie May 05 '17 at 18:48

1 Answers1

1

Your program could be better written like this. I know you solved your original issue, but you did note that you wouldn't mind a better way of resetting filters. Here's the better way:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)

         Dim ws As Worksheet
         ' Use 'ThisWorkbook' here since you only want to modify the workbook
         ' that the code is running in
         For Each ws In ThisWorkbook.Worksheets
             If ws.AutoFilterMode then ws.ShowAllData
         Next

    Cancel = False
End Sub

A tip for learning VBA, just because you can bypass errors with On Error Resume Next doesnt mean you should. More often than not, an error can be avoided by a simple boolean check. This prevents you from missing other potential issues, and is better coding practice. Even further, some people on SO won't bother trying to help if you're just ignoring errors outright.

Brandon Barney
  • 2,382
  • 1
  • 9
  • 18
  • I'm pretty sure I got the on error resume next from SO in the first place - thank you for the solution! I'll use this as my go-to for resetting filters – Selkie May 05 '17 at 18:48
  • It is possible, but it would have been someone who was still getting their legs underneath them in VBA. Once you get a strong sense of the language there should only be rare instances where it is justified. I am glad this helped you out though :). – Brandon Barney May 05 '17 at 19:01