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

How to automatically reapply auto-filter when data changes in Excel?

In Excel, when you apply the Filter function to filter data, the filter result will not be changed automatically with the data changes in your filtered data. For example, when I filter all Apples from the data, now, I change one of the filtered data to BBBBBB, but the result will not be changed as well as following screenshot shown. This article, I will talk about how to reapply auto-filter automatically when data changes in Excel.

doc auot refresh filter 1

Automatically reapply auto-filter when data changes with VBA code


arrow blue right bubble Automatically reapply auto-filter when data changes with VBA code

Normally, you can refresh the filter data by clicking the Reapply feature manually, but, here, I will introduce a VBA code for you to refresh the filter data automatically when data changes, please do as follows:

1. Go to the worksheet that you want to auto refresh filter when data changes.

2. Right click the sheet tab, and select View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module window, see screenshot:

VBA code: Auto reapply filter when data changes:

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

doc auot refresh filter 2

Note: In above code, Sheet 3 is the name of the sheet with auto-filter you use, please change it to your need.

3. And then save and close this code window, now, when you change the filtered data, the Filter function will be auto refreshed at once, see screenshot:

doc auot refresh filter 3


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)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
hello, how can i use all this in google finance? Tks
This comment was minimized by the moderator on the site
Nice.. really i need it
This comment was minimized by the moderator on the site
I I want a change on one sheet to cause multiple other sheets to autofilter, how do I change this code? Ex: SheetA is changed, which causes Sheet1, Sheet2, and Sheet3 to apply its autofilter. Thanks!
This comment was minimized by the moderator on the site
I am doing this for a front in sheet were it the cell is set to =sheet1!E6. It will not apply filter when it changes. If i change the number in the back sheet it adjust front but does not filter. If adjust the formula to filter it criteria it does reapply. What can i do?
This comment was minimized by the moderator on the site
Use this
Private Sub Work_Change(ByVal Target As Range)
Activesheet.AutoFilter.ApplyFilter
End Sub
This comment was minimized by the moderator on the site
I cannot get this to work for me at all. I am trying to take from a master sheet and have it only take the jobs that apply to certain project managers on each tab that is with their names. I also want it to auto refresh when I make changes.
This comment was minimized by the moderator on the site
this command all fake do nothing . totally try but no use of.
This comment was minimized by the moderator on the site
Works great and saves me a lot of time and messing about.. Really great tip.. Many thanks for your help
This comment was minimized by the moderator on the site
This solution works perfectly. Thanks for writing it up! If anyone is having trouble, there are a few things to consider.

First, the Worksheet_Change event is called on a sheet-by-sheet basis. This means if you have multiple sheets which have filters you need updated, you will need to respond to all those events. One Worksheet_Change subroutine for each worksheet, not one subroutine for the entire workbook (one exception - see note below).

Second, and a follow-on to the first, the code must be placed in the code module specific to the worksheet to be monitored. Its easy to (inadvertently) switch code modules once you get into the VB editor, so care must be taken to place it specific to the sheet you want to monitor for data changes.

Third, this is unconfirmed, but possibly a point of error. The example uses sheet names of "Sheet1", "Sheet2", etc. If you've renamed the sheets, you may need to update the code. Note in the example, Sheet7 has been given the name "dfdf". If you wanted to update the filter there, you'd need to use;
Sheets("dfdf").AutoFilter.ApplyFilter
not;
Sheets("Sheet7").AutoFilter.ApplyFilter

It might be good to update the article including an example with a renamed sheet.


Finally, if you want to monitor one sheet for data changes, but update filters on multiple sheets, then you only need one subroutine, placed in the code module of the worksheet you are monitoring. The code will look something like this;

# (code must be placed in the worksheet to be monitored for data changes)
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").AutoFilter.ApplyFilter
Sheets("Sheet2").AutoFilter.ApplyFilter
Sheets("Sheet3").AutoFilter.ApplyFilter
Sheets("Sheet4").AutoFilter.ApplyFilter
End Sub
This comment was minimized by the moderator on the site
Hello, Mike,
Thanks for your detailed explanation.
This comment was minimized by the moderator on the site
Great explanation, thank you.

But how do I trigger Sheets("Sheet3").AutoFilter.ApplyFilter when a new sheet is created?
Since I cant write the code you mentioned on a sheet that doesnt exist yet
This comment was minimized by the moderator on the site
Great, thank you!
This comment was minimized by the moderator on the site
works like a champ, and so simple. thank you very much!
This comment was minimized by the moderator on the site
This seems great. Can you tell me how to do the same for Sort, rather than Filter, please?
This comment was minimized by the moderator on the site
Hello, Chris,
May be, the following article can solve your problem, please view:

https://www.extendoffice.com/documents/excel/2592-excel-auto-sort-by-value.html

Please try it!
This comment was minimized by the moderator on the site
Hi, that works great, however only when manually changing data in the table.

I have a ‘top ten/leader board’ style filtered table which is populated from data entry on a separate worksheet (actually the data goes through 3 worksheets before getting to the table). When the data is changed in the data entry worksheet the leader board table figures updates however the filter doesn’t auto refresh.
Any ideas on how to do that?
Much Obliged.
Alex
This comment was minimized by the moderator on the site
I have she same problem. Can someone help us out?
This comment was minimized by the moderator on the site
I am having the same issue. I am trying to autofilter sheet 2 that contains data coming from sheet 1. it only works if i change the data on sheet 2, not on sheet 1.
Any thoughts on why this is not working and how to fix it?
This comment was minimized by the moderator on the site
Hi, this seems to work great but I am having problems when there are more than one filter on the same worksheet (tab). I converted the range of cells to a table to allow separate and multiple filters within the same worksheet. This example only appears to update one of the tables/filters. Any suggestions on how to update ALL tables/filters within a worksheet?

Many thanks,

Tom
This comment was minimized by the moderator on the site
Hi, Tom,
The code in this article works well for multiple tables within a worksheet, you just need to press Enter key after changing the data instead of click to other cell.
Please try it.
This comment was minimized by the moderator on the site
terima Kasih

sangat membantu
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
Brilliant and simple to do. Thanks so much!
This comment was minimized by the moderator on the site
Hi,

This code works great, thanks a lot.

I, however, have one small issue with it - if I change values in any cell that is not part of the table, I am presented with Runtime error saying:

"Run-time error '91':

Object variable or With block variable not set up"


I have options to Debug or End, option to Continue is greyed out. I can click on "End" and the code still works, however it is very annoying having to deal with this popup window after every change.

Anybody has similar experience or a suggestion about how to sort this?

Thanks!
This comment was minimized by the moderator on the site
Hello, David,
To solve your problem, you may apply the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
Hi Skyyang,


I have implemented your solution and it is indeed fixed.

Thanks a lot!
This comment was minimized by the moderator on the site
Hello I have had the same issue, pasted the new code and change the name of the sheet but then nothing happens, the filter doesn't update
This comment was minimized by the moderator on the site
same problem here as well, any updated solution?
This comment was minimized by the moderator on the site
Cant get this to work at all on office 365
any suggestions
This comment was minimized by the moderator on the site
Hi, thanks so much for the help. Something isn't working right for me. Here's the story.

Sheet1 has variable data. Sheet3 has static data and filter. Filter criteria on "Sheet3" comes from Sheet1. Sheet1 has data that comes from filtered results on Sheet3.

Sheet3 has code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1:U14").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A22:U23"), CopyToRange:=Range("A25:U26"), Unique:=False
End Sub

It works great if I do anything on Sheet3. No problems. Thank you!

At first I had code on Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Which resulted in the error "Runtime error 91, Object Variable or With Block not Set".

I changed the code based on comments to be:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
End Sub

Now I don't get an error, but the data on Sheet3 and therefore Sheet1 don't change. In other words, the event of applying the filter to Sheet3 doesn't occur when I make a change on Sheet1. It doesn't matter if I hit <return> or click on another cell after changing the Sheet3 filter criteria cell that is set on Sheet1.

As an aside, I expect that if I wanted to have multiple cells on Sheet1 that caused filters on Sheets 4 and 5 in addition to Sheet3, I would need the code on Sheet 1 to read:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Sheet3").AutoFilter.ApplyFilter
Sheets("Sheet4").AutoFilter.ApplyFilter
Sheets("Sheet5").AutoFilter.ApplyFilter
End Sub

Thanks again!
This comment was minimized by the moderator on the site
Hi,

This is a great bit of code thank you. The only issue I am having is I'm using a drop down on a separate chart sheet. If I manually change the value in the cell associated with the drop down, it works. But when I try to just use the drop down, it won't update. Any thoughts?
This comment was minimized by the moderator on the site
I actually have data from an other Excel file that got imported in a Excelsheet with the name "Database". Then I import this data in the same Excel file but in an other ExcelSheet "Overview". I want when the data changes in the orgininal source, that the filter applies in the sheet "Overview". Thank you in forward for the one who can help me :). P.S. cant use VBA in the firt excelsheet
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations