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.
Apply same filter to multiple worksheets with VBA code
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! |
Apply same filter to multiple worksheets with VBA code
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".
