Unhide Sheets Clear Filters Hide Sheets

[Talies]

Hello,

I am trying to combine the following three macros. My goal is upon close of the workbook, to unhide all hidden sheets, clear all filters on all pages, hide all sheets except one called Landing Page.

Macro 1

Sub vba_unhide_all_sheet()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Visible = False Then
ws.Visible = True
End If
Next ws
End Sub

Macro 2
Private Sub Workbook_beforeclose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If (Activesheet.AutoFilterMode And Activesheet.FilterMode) Or Activesheet.FilterMode Then
Activesheet.ShowAllData
End If
End With
Next ws

End Sub

Macro 3
Sub HideWorksheets()

Dim ws As Worksheet
For Each wst In ThisWorkbook.Worksheets

If ws.Name <> ThisWorkbook.ActiveSheet.Name Then

ws.Visible = xlSheetHidden

End If
Next ws
End Sub

I have tried various combinations of the above three macros to no avail. All would need to be in the Private Sub Workbook_beforeclose macro.

Any and all help is greatly appreciated!!

 

[Fluff]

Hi & welcome to MrExcel.
For your code to have any affect, you would need to save the workbook after it has run. Which may not please some users if they are deliberately trying to close the workbook without saving.

 

[Talies]

Hi & welcome to MrExcel.
For your code to have any affect, you would need to save the workbook after it has run. Which may not please some users if they are deliberately trying to close the workbook without saving.

Currently, if someone closes the workbook without saving, the filters will still be cleared, which is not a problem for any user (there are only 11) using the workbook (which is a database type workbook).
With all the 40+ worksheets hidden (individual worksheets can be opened by clicking a link on the Landing Page and are hidden again when user returns to the Landing Page), the filters will not be cleared upon close, which is what I am trying to do.

 

[Fluff]

If somebody saves the workbook with filters in place & some sheets unhidden, then you have to save the workbook again after removing filters etc. Otherwise the code will have no affect, but that means you have to save the workbook regardless of whether the user wants to.
Are you happy with that?