Reapply all filters on workbook open

Hello,

Have this problem whereby users are leaving filters applied in a workbook with multiple sheets.

This means that other users are opening the workbook and are unable to find data when they don't realise a filter has been applied.

Problem is, this workbook can be open by many users at one time, so a macro that clears and reapplies all filters when the workbook is opened would then clear filters for other users already in the workbook?

If this is not the case, is someone able to help me edit the below code to include "clear all filters on all sheets" then "reapply all filters on all sheets" when the workbook is opened?

Please Login or Register  to view this content.

Many Thanks

The line:

Please Login or Register  to view this content.

Will remove all selections from the filter but leave the filter applied to the data.

If there are no items filtered in the data then the above will result in an error so one workaround might be:

Please Login or Register  to view this content.

Hello,

Thanks for your reply. Think I may not have been clear in what I was asking.

When the user opens the workbook, the code should basically clear all the filters on all sheets so that nothing is hidden by a filter (or filtered out) on any sheet but ensure that the filter arrows are there in the header row so that they can use them to filter.

I found the below code which works when I assign it to a macro button, but I'd like similar to this to happen when the workbook is opened. I have no idea how to add the below into my original piece of code

Please Login or Register  to view this content.

Mark L: Not in this instance, if you create a table with a filter on and dont filter anything then:

Please Login or Register  to view this content.

Will return an error

However wrapped in the suggested On Error statement it will not error due to the resume next part of the statement.

On Error is not "Useless" in the given example, i agree there are other ways to get around the issue hence the use of the words "one workaround might be"

Originally Posted by Chris_M_1987

When the user opens the workbook, the code should basically clear all the filters on all sheets so that nothing is hidden by a filter (or filtered out) on any sheet but ensure that the filter arrows are there in the header row so that they can use them to filter.

That is what the below line is doing but to one sheet (remove all selections from filter but leave filter in tact):

Please Login or Register  to view this content.

You could try and paste the below into the ThisWorkbook code module:

Please Login or Register  to view this content.

Or without On Error:

Please Login or Register  to view this content.

Basically if I add the new bit of code to the bottom of my original code, it only clears the filters on some sheets. It doesn't work on 2 out of the 8 sheets on the workbook.

I'm not an advanced user and am relying on code created by other people.

Please Login or Register  to view this content.

Maybe try your code like the below:

Please Login or Register  to view this content.

Run-time error '1004':

ShowAllData method of Worksheet class failed

Marc L: I do apologise for the mistake in your name, it wasn't intentional.

Hi There

It's still not working on some sheets but I think I know why.

These sheets are protected, as they can't be accidentally overwritten. The sheet names are:

Master Data
Occasions Current 6 Months
Occasions Last 12 Months

Is there a way to remove the protection from these sheets, run the filter code and then reapply the protection (with the same settings)?

Please Login or Register  to view this content.

Kind Regards

What if you allow the autofilter when protecting the sheets by ticking "Use Autofilter", then change the Clear_fiter code to

Please Login or Register  to view this content.

That's not worked either unfortunately.

I checked and autofilter was ticked on all the protected sheets.