How to apply same filter to multiple sheets In Excel?
It may be easy for us to apply the Filter function to filter data in a worksheet, but, sometimes, you may need to filter across multiple worksheets which have common data formatting with the same filter criteria. To filter them one by one will waste lots of time, here, I can introduce an easy way to solve it at once.
Filter data by multiple criteria or other specific condition, such as by text length, by case sensitive, etc.
Kutools for Excel’s Super Filter feature is a powerful utility, you can apply this feature to finish the following operations:
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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 Module Window.
VBA code: Apply same filter to multiple worksheets:
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".
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agohow to eliminate filtered rows?
To post as a guest, your comment is unpublished.· 7 months agoHow to use "Filter Between" Function, for the same multiple sheets?
(Example: Time range)
To post as a guest, your comment is unpublished.· 7 months agoCan we use cell name like Product instead of A1 in : xWs.Range("A1").AutoFilter 1, "=KTE"
I have different column and some names comes in different column.
To post as a guest, your comment is unpublished.· 9 months agoHI, does the super filter across the entire workbook automatically reapply whenever theres an update of the file?
To post as a guest, your comment is unpublished.· 1 years agoThank you for the code!! is there a way to change the "=KTE" to use a value from a List or Drop down list?