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]

Although you say you want to AutoFilter on column M, you haven't said what value(s) you want to filter on. That is what values in that column to display or hide.

 

[jim may]

Res = Inputbox("Enter Desired Criteria")
Selection.AutoFilter Field:=13, Criteria1:=Res, Operator:=xlAnd

 

[Beachbum]

Although you say you want to AutoFilter on column M, you haven't said what value(s) you want to filter on. That is what values in that column to display or hide.

Hi Peter. You are right. I want to filter on blank cells only. I won't need an input box as Jim suggests.

 

[jim may]

Selection.AutoFilter Field:=13, Criteria1:="", Operator:=xlAnd

maybe, without testing,,,,,

 

[Beachbum]

Hi Jim,
Looks familiar! I have tried some like that but it did not run (mostly 1004 error). Can you tell me where in the code I would have to insert that line and maybe what else should be added to make it work?

 

[jim may]

Try this code - Note the following FIRST! Using the For Each Worksheets will cause the code to continue thru ALL Worksheets (If any exist) beyond your 2 -- So particularly if you have blank worksheets after Sheet1 and Sheet2 then Delete them; Also the Cell A5 below (may need changing) -- this cell must be somewhere within your AF range ON BOTH SHEETS, otherwise you will get an error.

Sub tester()
For Each w In Worksheets
w.Unprotect
If w.FilterMode Then w.ShowAllData
w.Range("A5").AutoFilter Field:=3, Criteria1:=""
w.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True
Next w
End Sub

 

[jim may]

Woops,
Change above line to:

w.Range("A5").AutoFilter Field:=13, Criteria1:=""

 

[Peter_SSs]

I may have missed something but I can't see where the "A5" reference came from. My suggestion is:

<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> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        <SPAN style="color:#00007F">With</SPAN> ws<br>            .Unprotect<br>            .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><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>            .Range("M3", .Range("M" & Rows.Count).End(xlUp)).AutoFilter _<br>                Field:=1, Criteria1:="="<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            .Protect<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Beachbum]

Jim, thanks. I tried your suggestion but I still got the run-time error 1004 (AutoFilter method of Range class failed), very likely because I have other sheets in the workbook besides those two with data. So I went ahead and tried Peter's code too ...

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?

Thanks again, this really helps!