Macro to unfilter columns on exit of a workbook

[BigCoolBob]

Hi

I have a problem in that I have a number of users with access to a workbook that contains scheduling information across a number of worksheets. Each of these users obviously filters the data to highlight their own specific scheduled work, however, very rarely do they unfilter the list once they have finished, and many other users go in, see the filtered list and exit without getting the work that they need to do as they cant see their name.

I have created pop up boxes on entry and exit into the workbook reminding them to unfilter the list before they save and close, however, these messages go largely unheeded.

Is there a way to add a macro into the workbook that will automatically unfilter the columns in all worksheets when the workbook is closed, or is there a way to unfilter the list when the next person opens the workbook? I am currently going into the workbook multiple times a day just to remove the filters otherwise work isnt being picked up and therefore not being done.

Many thanks for any assistance you can give.

Ian

 

[wigi]

Sure. For instance

On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        ws.AutoFilterMode = False
    Next

 

[R Pelle]

You'll probably need to adapt it to suit your needs
Add this to Workbook/BeforeClose in VB.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End Sub

 

[BigCoolBob]

Thanks for your replies.

Wigi, I have got yours in my workbook, and it removes the filter completely rather than just unfiltering it and leaving the filter arrows in the sheet. Is there a way to unfilter it rather than remove the filter arrows completely?

Thanks again

 

[wigi]

Then use:

On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
          If ws.FilterMode Then ws.ShowAllData
    Next