VBA Run-time error 1004 when auto-filtering

[stevieinselby]

I have a shared workbook with frozen frames and autofilters. You know what's coming next ... yes, other people frequently leave filters set, and that messes things up when the next person comes along, takes the filters off, and now the first umpteen rows are frozen and they can't scroll. So I have put code into Workbook_Open that unfilters the page and resets the frozen frames ... all working fine. I also want to set one particular filter, and after about 100 attempts I have managed to get that working ... but only when the workbook is not shared. As soon as I share the workbook, I get the dreaded Run-time error '1004': Application-defined or object-defined error. And now because the workbook is shared, I can't even debug it to find where the error is. The worksheet is protected, I don't know if that makes a difference.

With Sheets("Sheet1")
.Activate
ActiveSheet.AutoFilter.ShowAllData
ActiveWindow.FreezePanes = False
ActiveWindow.SplitColumn = 3
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
.Range("C1:BC1").AutoFilter
.Range("C1:BC1").AutoFilter Field:=8, Criteria1:="M"
End With

If I put On Error Resume Next then it does everything in Workbook_Open except applying the filter Field8="M"

Any suggestions as to how to make it work? Or am I trying to achieve something that Excel makes impossible?

 

[Fluff]

Is there a filter already in place?

 

[stevieinselby]

Is there a filter already in place?

AutoFilter is always on, and it shouldn't ever be possible for anyone to disable it. Sometimes people leave filters applied when saving the file, and that's what causes problems the next time someone opens it. The most important thing is that any time someone opens the file, it automatically clears any filters previously set, resets the freeze-frames, and leaves AutoFilter enabled, which I can do.

What I would like to do, as an added bonus, is to automatically have one filter applied whenever someone opens the file, so that it doesn't show expired items. There is not normally any need to show expired items other than when I am doing periodic maintenance, so it makes sense to have those items hidden under normal circumstances – unlike the other filters that people will use when looking up particular records or matches. But if there's no way to achieve it on a protected and shared workbook then I can live with it as it is.

 

[Youngdand]

It was yours that deactivated the filter altogether, but Fluff's didn't solve it either.

I did edit my code slightly to set the range to c1:BC & LR (the last row), so just double check that you are using the latest iteration. When testing, i only tested on c1& LR, it worked fin for me, however i have not tested on a protected sheet. that said, even unprotected, with the code you supplied i received the 1004 error.

 

[Youngdand]

Try this. I have it working in protected sheets.

Sub filtersheets()

Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Sheet1")
.Activate
ActiveSheet.Protect , userinterfaceonly:=True
ActiveSheet.AutoFilter.ShowAllData
ActiveWindow.FreezePanes = False
ActiveWindow.SplitColumn = 3
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
.Range("C1:BC" & LR).AutoFilter , Field:=8, Criteria1:="M"
End With
End Sub