I wouldn't put this into the _beforeclose event.

If you do put it there, then the user still has to save the workbook after that
change. And if the user didn't want to save, the filter wouldn't be cleared.

If you added a Save to your procedure, then it may mess up the workbook--if the
user opens the workbook and deletes 18 worksheets and wants to close without
saving, your code would mess that up.

Instead of clearing the filter when closing the workbook, I'd clear it when it
was opened:


If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
If .FilterMode Then
.ShowAllData
End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kevin wrote:

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin

--

Dave Peterson