Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

Як автоматично повторно застосувати автоматичний фільтр при зміні даних у Excel?

У Excel при застосуванні фільтр функція фільтрування даних, результат фільтрування не буде автоматично змінений із зміною даних у ваших відфільтрованих даних. Наприклад, коли я фільтрую всі яблука з даних, зараз я змінюю один із відфільтрованих даних на BBBBBB, але результат не буде змінений так само, як показано на наступному скріншоті. У цій статті я розповім про те, як автоматично застосовувати автоматичний фільтр автоматично, коли дані змінюються в Excel.

doc auot фільтр оновлення 1

Автоматично повторно застосовувати автоматичний фільтр, коли дані змінюються за допомогою коду VBA


стрілка синя права міхур Автоматично повторно застосовувати автоматичний фільтр, коли дані змінюються за допомогою коду VBA

Зазвичай ви можете оновити дані фільтра, натиснувши функцію Повторно застосувати вручну, але тут я представив код VBA для автоматичного оновлення даних фільтра при зміні даних, будь-ласка, виконайте такі дії:

1. Перейдіть на аркуш, який потрібно автоматично оновити, фільтр при зміні даних.

2. Клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню, що з’явиться Microsoft Visual Basic для додатків вікно, скопіюйте та вставте наступний код у порожнє вікно модуля, див. скріншот:

Код VBA: Автоматичне повторне застосування фільтра при зміні даних:

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

doc auot фільтр оновлення 2

примітки: У наведеному вище коді, 3 Sheet - це назва аркуша з автоматичним фільтром, який ви використовуєте, будь ласка, змініть його відповідно до своїх потреб.

3. А потім збережіть і закрийте це вікно коду, тепер, коли ви зміните відфільтровані дані, фільтр функція буде автоматично оновлена ​​відразу, див. знімок екрана:

doc auot фільтр оновлення 3


Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

Вкладка Office забезпечує інтерфейс з вкладками для Office і значно спрощує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Kay · 5 months ago
    Thank you. Works great!
  • To post as a guest, your comment is unpublished.
    Dom · 1 years ago
    Hey this works great but I'd like to re-apply the autofilter to multiple sheets if possible.
    I have 3 sheets (Enquiry, Booked, No sale)

    What I'm trying to do is essentially move the data when I change the item status code:
    "Enquiry" is filtered to show 'In progress' only, "Booked" is filtered to show 'Booked' only etc

    Is there a way to re-apply filter to multiple worksheets when making a change on "enquiry"
  • To post as a guest, your comment is unpublished.
    Danny · 2 years ago
    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
  • To post as a guest, your comment is unpublished.
    Anthony · 2 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Jetson · 2 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Neil · 2 years ago
    Cant get this to work at all on office 365
    any suggestions
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    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!
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      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!
      • To post as a guest, your comment is unpublished.
        David · 2 years ago
        Hi Skyyang,


        I have implemented your solution and it is indeed fixed.

        Thanks a lot!
        • To post as a guest, your comment is unpublished.
          adrien · 6 months ago
          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
  • To post as a guest, your comment is unpublished.
    joe · 2 years ago
    Brilliant and simple to do. Thanks so much!
  • To post as a guest, your comment is unpublished.
    Puly · 2 years ago
    This 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.
    Rizqi · 3 years ago
    terima Kasih

    sangat membantu
  • To post as a guest, your comment is unpublished.
    Tom · 3 years ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      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.
  • To post as a guest, your comment is unpublished.
    AWilson · 3 years ago
    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
    • To post as a guest, your comment is unpublished.
      Danny · 2 years ago
      I have she same problem. Can someone help us out?
  • To post as a guest, your comment is unpublished.
    Chris · 3 years ago
    This 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.
      skyyang · 3 years ago
      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!
  • To post as a guest, your comment is unpublished.
    steve@voiceinwilderness.info · 3 years ago
    works like a champ, and so simple. thank you very much!
  • To post as a guest, your comment is unpublished.
    Mike T · 3 years ago
    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
    • To post as a guest, your comment is unpublished.
      Dirk · 4 months ago
      Great, thank you!
    • To post as a guest, your comment is unpublished.
      Luke H · 3 years ago
      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
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Mike,
      Thanks for your detailed explanation.
  • To post as a guest, your comment is unpublished.
    Dave J · 4 years ago
    Works 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.
    Asad · 4 years ago
    this command all fake do nothing . totally try but no use of.
  • To post as a guest, your comment is unpublished.
    SABRINA · 4 years ago
    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.
  • To post as a guest, your comment is unpublished.
    Brian · 4 years ago
    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?
    • To post as a guest, your comment is unpublished.
      Asad · 4 years ago
      Use this
      Private Sub Work_Change(ByVal Target As Range)
      Activesheet.AutoFilter.ApplyFilter
      End Sub
  • To post as a guest, your comment is unpublished.
    Drew · 4 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Arif · 4 years ago
    Nice.. really i need it
  • To post as a guest, your comment is unpublished.
    VINICIUS · 4 years ago
    hello, how can i use all this in google finance?

    Tks