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.
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 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".
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 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!