none
Reset filter before saving a worksheet RRS feed

  • Question

  • Dears,

    i need help, that i have a code that let me reset filter before saving a worksheet, but i have an error while some of worksheets are very hidden, i want release the code even the sheets are very hidden

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,Cancel As Boolean) Dim wks As Worksheet For Each wks In Worksheets If wks.FilterMode = True Then wks.ShowAllData End If Next wks End Sub

    thanks a lot,

    Monday, January 19, 2015 9:24 AM

Answers

  • Hi Ahmed M Abdel Kader,

    I am glad to here that the issues was fixed. Would you mind sharing the solution with us?

    Also the code in the previous post works well for me and as far as I test, it also worked in before save event, you  can try the code below to see whether it works for you:

    Sub ShowData()
    Dim aSheet As Worksheet
    Set aSheet = Application.Sheets("sheet1")
    If aSheet.AutoFilterMode Then
        For Each afilter In aSheet.AutoFilter.Filters
            If afilter.On Then
                 aSheet.ShowAllData
            End If
        Next
    End If
    
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ShowData
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 22, 2015 2:00 AM
    Moderator

All replies

  • Hi Ahmed MAbdel Kader,

    Based on the description, you want to reset the filter for a worksheet which is very hidden.

    As far as I test in Excel 2013, the visibility doesn't make the showAllData faild. The method only failed when the data alread is show.

    To detect whether there are filters acitve on a worksheet, we can loop the fillters and check the Filter.On. Here is a sample for your reference:

    Sub ShowData()
    Dim aSheet As Worksheet
    Set aSheet = Application.Sheets("sheet1")
    If aSheet.AutoFilterMode Then
        For Each afilter In aSheet.AutoFilter.Filters
            If afilter.On Then
                 aSheet.ShowAllData
            End If
        Next
    End If
    
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 20, 2015 6:02 AM
    Moderator
  • Hi Fie,

    the problem was occur when exit the workbook then hit save, the error highlight this line

    wks.ShowAllData

    and i got it solved, thanks a lot for your follow up

    cheers,

     

     
    Tuesday, January 20, 2015 8:28 AM
  • Hi Ahmed M Abdel Kader,

    I am glad to here that the issues was fixed. Would you mind sharing the solution with us?

    Also the code in the previous post works well for me and as far as I test, it also worked in before save event, you  can try the code below to see whether it works for you:

    Sub ShowData()
    Dim aSheet As Worksheet
    Set aSheet = Application.Sheets("sheet1")
    If aSheet.AutoFilterMode Then
        For Each afilter In aSheet.AutoFilter.Filters
            If afilter.On Then
                 aSheet.ShowAllData
            End If
        Next
    End If
    
    End Sub
    
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ShowData
    End Sub
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, January 22, 2015 2:00 AM
    Moderator