Show all data in auto filter upon workbook close/save
Posted by Bob on July 06, 2001 10:56 AM
Can someone help me with code for the following request please:
I have multiple worksheets in a workbook, and on each worksheet, I have autofilters in row 4 for columns A through F. In my custom save workbook macro, I would like to cycle through all worksheets (except worksheet 1), and show all of the options, in case the user filtered data on any of the sheets for viewing or printing. I would still like to keep the autofilters, but just make sure they show all data when the workbook is opened again. Thank you so much in advance.
Posted by Scott S on July 06, 2001 11:18 AM
You can add this to your macro before it saves the workbook.
Sheets("Sheet2").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
You could do this for every sheet.
Posted by Bob on July 06, 2001 11:49 AM
That was fast, thanks, here's what I did.
Thanks Scott, I modified it to the following, and called for it in my save and exit macros, for anyone who might need it:
Public Sub UNDOFILTERS()
Dim AW As String
AW = ActiveSheet.Name
Application.ScreenUpdating = False
For WSB = 2 To Worksheets.Count
Sheets(WSB).Select
With Selection
.AutoFilter Field:=1
.AutoFilter Field:=2
.AutoFilter Field:=3
.AutoFilter Field:=4
.AutoFilter Field:=5
.AutoFilter Field:=6
End With
Next WSB
Sheets(AW).Select
Application.ScreenUpdating = True
End Sub
