¿Cómo volver a aplicar automáticamente el filtro automático cuando los datos cambian en Excel?
En Excel, cuando aplica el Filtrar función para filtrar datos, el resultado del filtro no se cambiará automáticamente con los cambios de datos en sus datos filtrados. Por ejemplo, cuando filtro todas las manzanas de los datos, ahora, cambio uno de los datos filtrados a BBBBBB, pero el resultado no se modificará, así como tampoco la siguiente captura de pantalla. En este artículo, hablaré sobre cómo volver a aplicar automáticamente el filtro automático cuando los datos cambien en Excel.
Normalmente, puede actualizar los datos del filtro haciendo clic en la función Reaplicar manualmente, pero, aquí, presentaré un código VBA para que actualice los datos del filtro automáticamente cuando los datos cambien, por favor haga lo siguiente:
1. Vaya a la hoja de trabajo que desea actualizar automáticamente cuando los datos cambien.
2. Haga clic con el botón derecho en la pestaña de la hoja y seleccione Ver código desde el menú contextual, en el emergente Microsoft Visual Basic para aplicaciones ventana, copie y pegue el siguiente código en la ventana del Módulo en blanco, vea la captura de pantalla:
Código VBA: vuelve a aplicar el filtro automáticamente cuando los datos cambian:
Private Sub Worksheet_Change(ByVal Target As Range) Sheets("Sheet3").AutoFilter.ApplyFilter End Sub
Nota: En el código anterior, Hoja 3 es el nombre de la hoja con filtro automático que usa, por favor cámbiela a su necesidad.
3. Y luego guarde y cierre esta ventana de código, ahora, cuando cambie los datos filtrados, Filtrar la función se actualizará automáticamente de una vez, vea la captura de pantalla:
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.· 2 months agoCant get this to work at all on office 365
To post as a guest, your comment is unpublished.· 4 months agoHi,
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?
To post as a guest, your comment is unpublished.· 3 months agoHello, David,
To solve your problem, you may apply the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Please try it, hope it can help you!
To post as a guest, your comment is unpublished.· 5 months agoBrilliant and simple to do. Thanks so much!
To post as a guest, your comment is unpublished.· 7 months agoThis does not work with filter based on list selection https://www.extendoffice.com/documents/excel/4113-excel-filter-based-on-list-selection.html
To post as a guest, your comment is unpublished.· 10 months agoterima Kasih
To post as a guest, your comment is unpublished.· 1 years agoHi, 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?
To post as a guest, your comment is unpublished.· 1 years agoHi, 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?
To post as a guest, your comment is unpublished.· 1 years agoThis seems great. Can you tell me how to do the same for Sort, rather than Filter, please?
To post as a guest, your comment is unpublished.· 1 years agoHello, Chris,
May be, the following article can solve your problem, please view:
Please try it!
To post as a guest, your comment is unpublished.· 1 years agoworks like a champ, and so simple. thank you very much!
To post as a guest, your comment is unpublished.· 1 years agoThis 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;
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)
To post as a guest, your comment is unpublished.· 1 years agoGreat 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
To post as a guest, your comment is unpublished.· 1 years agoHello, Mike,
Thanks for your detailed explanation.
To post as a guest, your comment is unpublished.· 2 years agoWorks great and saves me a lot of time and messing about.. Really great tip.. Many thanks for your help
To post as a guest, your comment is unpublished.· 2 years agothis command all fake do nothing . totally try but no use of.
To post as a guest, your comment is unpublished.· 2 years agoI 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.
To post as a guest, your comment is unpublished.· 2 years agoI 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?
To post as a guest, your comment is unpublished.· 2 years agoI 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.
To post as a guest, your comment is unpublished.· 2 years agoNice.. really i need it
To post as a guest, your comment is unpublished.· 2 years agohello, how can i use all this in google finance?