Autofilter when workbook opens

[Beachbum]

Hi,
My workbooks contains 2 worksheets with data and autofilters on for each column in use. When my workbook opens I am trying to reset the autofilters of each worksheet in the workbook and to filter the data according to one criteria in one column.
This is what I have that works to reset each worksheet but I haven't been able to figure out how to subsequently filter each worksheet.

Private Sub Workbook_Open()
Application.ScreenUpdating = False
For Each w In Worksheets
w.Unprotect
If w.FilterMode Then w.ShowAllData
w.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Next w
Application.ScreenUpdating = True
End Sub

The autofilter sits in A3 to N3 and I am trying to filter according to column M (i.e. 13th column)

I'd appreciate al the help I can get! Thanx for taking the time.

 

[Peter_SSs]

Peter, thanks too. Your code works but it removes the Autofilter from the worksheets that had Autofilter on. So I need to make sure that after it has been filtered when opened the original autofilter arrow are still (or back) in the top of the columns.

Any suggestions?

Tell us more about your sheets and AutoFilters.

You originally said two sheets. Is that still the case?

Does each sheet have data in and autofilter on the same columns? If so which columns? If not, build a picture of the sheets for us so we can imagine/reproduce what you have.

You say you need the AutoFilter arrows in the top of the columns. Does that still mean row 3 as in your original post?

 

[Beachbum]

I should have been more specific, I apologize.

The workbook has 4 worksheets.

First 1 has no data, no filters, just a few hyperlinks to the other sheets.
2nd and 3rd are identical in build up: 14 columns, first 3 rows are the headers and titles and the autofilters are in the 3rd row from column A to N (so all 14).

Basically the sheets is a list of activities with in column 13 the completion date. I would like the workbook to open and only be filtered according to column 13 on empty cells independent of how it was saved (hence the "showAll" part to reset all the filters) but still have the autofilter in all column in the original place (i.e. 3rd row from col A to N) for the user to use.

Hope this helps!

 

[Peter_SSs]

I should have been more specific, I apologize.

The workbook has 4 worksheets.

First 1 has no data, no filters, just a few hyperlinks to the other sheets.
2nd and 3rd are identical in build up: 14 columns, first 3 rows are the headers and titles and the autofilters are in the 3rd row from column A to N (so all 14).

Basically the sheets is a list of activities with in column 13 the completion date. I would like the workbook to open and only be filtered according to column 13 on empty cells independent of how it was saved (hence the "showAll" part to reset all the filters) but still have the autofilter in all column in the original place (i.e. 3rd row from col A to N) for the user to use.

Hope this helps!

Ok, still didn't tell us about the fourth sheet so I'll ignore it. This code is not designed to be robust and will likely fail if the sheet order is changed, there are AutoFilters in place that do not extend at least from col A to col M. Let's just see if it now does what you want in the current workbook.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> AFRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> s = 2 <SPAN style="color:#00007F">To</SPAN> 3<br>        <SPAN style="color:#00007F">With</SPAN> Sheets(s)<br>            .Unprotect<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> AFRange = .AutoFilter.Range<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> AFRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> AFRange<br>                    .AutoFilter<br>                    .AutoFilter Field:=13, Criteria1:="="<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> AFRange = <SPAN style="color:#00007F">Nothing</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            .Protect<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> s<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>