Note: The other languages of the website are Google-translated. Back to English
Log in Register

 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.

Apply same filter to multiple worksheets with VBA code

arrow blue right bubble 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.

doc filter multiple sheets 1

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:

doc filter multiple sheets 2

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:

  • Filter data with multiple criteria;              Filter data by text length;
  • Filter data by upper / lower case;             Filter date by year / month / Day / week / quarter

doc-super-filter1

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!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (36)
No ratings yet. Be the first to rate!
Guy
Guy
This comment was minimized by the moderator on the site
how do you apply this filter formula: to a range of sheets? i have a workbook that has data on the first 5 sheets, and the workbook has 200 sheets i only need the filter to be applied to sheet 6-200. 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
Reply
Cosmicbugaboo
Cosmicbugaboo     Guy
This comment was minimized by the moderator on the site
Sub apply_autofilter_across_worksheets() Dim J As integer On Error Resume Next For J = 6 to Worksheets(worksheets.count) Thisworkbook.Sheets(J).Range("A1").Autofilter 1, "=KTE" Next End Sub
Reply
AHMAD KALBOUNEH
AHMAD KALBOUNEH
This comment was minimized by the moderator on the site
Dear, i want to filter more than one value, i used this xWs.Range("B1").AutoFilter 2, "=223AM", xlOr, "=113IR", xlOr, "=003IR" and it worked but when i increase the wanted values to >2 an error occurs. this command i use xWs.Range("B1").AutoFilter 2, "=223AM", xlOr, "=113IR", xlOr, "=003IR", xlOr, "=019IR", xlOr, "=311IR", xlOr, "=518ZA", xlOr, "=223AM", xlOr, "=592IR" thanks,
Reply
KABILAN
KABILAN
This comment was minimized by the moderator on the site
while filtering single word in multiple sheets it does not shows filtered data....all datas are hided.pleace resolve this.
Reply
Grace
Grace
This comment was minimized by the moderator on the site
Hi,


My data is in Column E and when I apply this filter with Range("E1") it still filters column A


How can I troubleshoot?
Thanks
Reply
VBA
VBA     Grace
This comment was minimized by the moderator on the site
You just have to write the Column number in AutoFilter, in this case: xWs.Range("E1").AutoFilter 5,
Reply
Tiamari
Tiamari     VBA
This comment was minimized by the moderator on the site
THANK YOU!!!!
Reply
GG
GG     Grace
This comment was minimized by the moderator on the site
DELETE THE COLUMN A LOL
Reply
Donovan Hubbart
Donovan Hubbart
This comment was minimized by the moderator on the site
How do execute if the filter is on a different column on each sheet?
Reply
donovan
donovan
This comment was minimized by the moderator on the site
How can you use the result of the auto-filter to set the filter for each other sheet instead of using =(specific name)
Reply
Justin
Justin
This comment was minimized by the moderator on the site
Can I use this to filter pivot tables in multiple sheets if all the pivots tables are from same data source and set up using same criteria?
Reply
Mahesh
Mahesh
This comment was minimized by the moderator on the site
Is there any option to filter by colors?
Reply
skyyang
skyyang     Mahesh
This comment was minimized by the moderator on the site
Hi, Mahesh,
If you want to filter data by color, may be the following two articles can help you! Please view them, hope it can help you!

https://www.extendoffice.com/documents/excel/912-excel-filter-by-cell-color.html
https://www.extendoffice.com/documents/excel/3383-excel-filter-by-multiple-colors.html
Reply
Report
Monika
Monika
This comment was minimized by the moderator on the site
Hi,


How this works if Product column is presented in different places in other sheets ?


In sheet1, it is A1 in sheet2 it is in B1 and in sheet3 it is A1 again ?

Thanks,
Monika
Reply
Matthew
Matthew
This comment was minimized by the moderator on the site
Is there a way to change the "=KTE" part for each sheet with a contain filter that is based on the sheet's name?
Reply
Sebastian
Sebastian
This comment was minimized by the moderator on the site
Thank you for the code!! is there a way to change the "=KTE" to use a value from a List or Drop down list?
Reply
jarred
jarred
This comment was minimized by the moderator on the site
HI, does the super filter across the entire workbook automatically reapply whenever theres an update of the file?
Reply
Suyash
Suyash
This comment was minimized by the moderator on the site
Can 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.
Reply
Report
Suyash
Suyash     Suyash
This comment was minimized by the moderator on the site
I have used filter in every sheet. And I want to change s.no from 1 2 3 serially in every sheet after filter.


Please let me know if you have vba code for it
Reply
Report
Sibi
Sibi
This comment was minimized by the moderator on the site
How to use "Filter Between" Function, for the same multiple sheets?

(Example: Time range)
Reply
berenice
berenice
This comment was minimized by the moderator on the site
how to eliminate filtered rows?
Reply
Pedjaa
Pedjaa
This comment was minimized by the moderator on the site
Dear,

How to filter with multiple conditions?
For example, I want to filter in order above 50 but only KTE products.

Thank you in advance.
Reply
skyyang
skyyang     Pedjaa
This comment was minimized by the moderator on the site
Hello, Pedjaa,
To filter across multiple sheets based on two conditions, please apply the following code:
Sub apply_autofilter_across_worksheets()

'Updateby Extendoffice

Dim xWs As Worksheet

On Error Resume Next

For Each xWs In Worksheets

With xWs.Range("A1")

.AutoFilter Field:=1, Criteria1:="KTE" 'Column A equals "KTE"

.AutoFilter Field:=2, Criteria1:=">500" 'Column B greater than 500

End With

Next

End Sub

Please try, hope it can help you!
Reply
Report
Tan
Tan
This comment was minimized by the moderator on the site
even if i replace A1 with G1, it pits the filter on A1 column
Reply
skyyang
skyyang     Tan
This comment was minimized by the moderator on the site
Hello, Tan,
You just replace A1 with G1, at the same time, you should change the column number 1 to column number 7, as below code:
xWs.Range("G1").AutoFilter 7, "=KTE"
Please try, thank you!
Reply
Report
Manuela
Manuela
This comment was minimized by the moderator on the site
Hello,
is it possible to filter based on Column Name, rather than number (as my data is in different colums in the different worksheets)?
Thank youManuela
Reply
Anthony A Navia
Anthony A Navia
This comment was minimized by the moderator on the site
I want to filter with the same condition "KTE", but if it does not show up in a sheet to no show any data.
Reply
Hemraj Gelli
Hemraj Gelli
This comment was minimized by the moderator on the site
Hi ,
I want filter criteria based on a cell value instead of "KTE" is it possible ???
Reply
skyyang
skyyang     Hemraj Gelli
This comment was minimized by the moderator on the site
Hello, Hemraj,To filter the data based on a specific cell value instead of the fixed text, please apply the below code:<div data-tag="code">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, Sheet1.Range("E2")
Next
End SubNote: Please change the Sheet1 and E2 within this Sheet1.Range("E2") script to your own sheet and cell value.Please try, hope it can help you!
Reply
Report
Hemraj Gelli
Hemraj Gelli     skyyang
This comment was minimized by the moderator on the site
Hi Skyyang,
Thanks for your valuble and Fablous support , i tried to use the same formulae but it doesnt worked for what might be the reason . The following is the code which i am using for my excel sheet.
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, Sheet2.Range("C4")
Next
End Sub
Reply
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations