Question

How many filters can be applied on a worksheet at a time?

Answer 1

you can do this by :

For example, I have four worksheets as following screenshot need to be filtered with the same criteria that the Product = KTE at once.



There is no direct way to filter data in multiple sheets in Excel, but, the following VBA code can help you to finish this job, please do as follows:

1. Hold down the ALT + F11 keys, then it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the ModuleWindow.

VBA code: Apply same filter to multiple worksheets:
we

Sub apply_autofilter_across_worksheets()

'Updateby Extendoffice 20160623

    Dim xWs As Worksheet

    On Error Resume Next

    For Each xWs In Worksheets

        xWs.Range("A1").AutoFilter 1, "=KTE"

    Next

End Sub

3. And then press F5 key to run this code, all products of KTE have been filtered in all worksheets at once, see screenshot:



Note: Within above code, in this xWs.Range("A1").AutoFilter 1, "=KTE"script, A1 and =KTE indicates the column and criteria which you want to filter based on, the number 1 is the column number you filtered based on, you can change them to your need. For example, if you want to filter the order greater than 50, you just need to modify this script as this: xWs.Range("B1").AutoFilter 2, ">50".