Loncat ke daftar isi utama

Bagaimana cara menerapkan kembali filter otomatis saat data berubah di Excel?

Di Excel, saat Anda menerapkan file Filter berfungsi untuk memfilter data, hasil filter tidak akan berubah secara otomatis dengan adanya perubahan data pada data yang Anda filter. Misalnya, ketika saya memfilter semua Apel dari data, sekarang, saya mengubah salah satu data yang difilter menjadi BBBBBB, tetapi hasilnya tidak akan berubah seperti gambar berikut yang ditampilkan. Artikel ini, saya akan berbicara tentang cara menerapkan kembali filter otomatis secara otomatis saat data berubah di Excel.

filter penyegaran otomatis doc 1

Terapkan kembali filter otomatis secara otomatis saat data berubah dengan kode VBA


panah gelembung kanan biru Terapkan kembali filter otomatis secara otomatis saat data berubah dengan kode VBA

Biasanya, Anda dapat menyegarkan data filter dengan mengklik fitur Terapkan kembali secara manual, tetapi, di sini, saya akan memperkenalkan kode VBA agar Anda dapat menyegarkan data filter secara otomatis saat data berubah, lakukan hal berikut:

1. Masuk ke lembar kerja yang ingin Anda refresh otomatis saat data berubah.

2. Klik kanan tab lembar, lalu pilih Lihat kode dari menu konteks, di-pop-out Microsoft Visual Basic untuk Aplikasi jendela, silakan salin dan tempel kode berikut ke jendela Modul kosong, lihat tangkapan layar:

Kode VBA: Terapkan kembali filter secara otomatis saat data berubah:

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

filter penyegaran otomatis doc 2

Note: Dalam kode di atas, Lembar 3 adalah nama sheet dengan filter otomatis yang Anda gunakan, harap ubah sesuai kebutuhan Anda.

3. Dan kemudian simpan dan tutup jendela kode ini, sekarang, saat Anda mengubah data yang difilter, file Filter fungsi akan disegarkan secara otomatis sekaligus, lihat tangkapan layar:

filter penyegaran otomatis doc 3

Alat Produktivitas Kantor Terbaik

Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

tab kte 201905


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (39)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Finally got the filter to update automatically. My scenario included a filter on sheet "Master" that had a formulas that depended on other sheets (named 1, 2,...,21).

Paste this in the source code into the "Master" sheet.

Private Sub Worksheet_Calculate()
On Error Resume Next
Application.EnableEvents = False
Me.AutoFilter.ApplyFilter
Application.EnableEvents = True
End Sub


Paste this in the source code of each sheet that feeds data into the master sheet. (Sheet 21 shown from my scenario)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets("Master").AutoFilter.ApplyFilter
End Sub
This comment was minimized by the moderator on the site
Its not working if able changing by itslef, like when you linking data from another app, can someone help me fix this please
This comment was minimized by the moderator on the site
Pasted it as shown, but not working. The sheet I need updated is an invoice that has formulas that are pulling from another sheet. Since the blank cells all have formulas in them, is that preventing it from working? Seems simple for it to work, so I'm getting frustrated with myself.
This comment was minimized by the moderator on the site
2022 update!
Just add this to the sheets you want it applied too.

Private Sub Worksheet_Change(ByVal Target As Range)
AutoFilter.ApplyFilter
End Sub
Rated 5 out of 5
This comment was minimized by the moderator on the site
My input data is on the first sheet of the book, and the table in which the filter is applied is on another sheet of the same book. When I change the data on the first sheet, the filter on the second sheet should be updated. But if at the time of changing the data, a cell in the range of this table is not selected, then I get an error:"Run-time error '91': Object variable or With block variable not set". Therefore, i am should always check that the cursor is in a table range cell and not in any other place on the sheet. How to fix it?Solved:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("name of sheet").ListObjects("name of table").AutoFilter.ApplyFilter
End Sub
This comment was minimized by the moderator on the site
Thank you. Works great!
This comment was minimized by the moderator on the site
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"
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
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
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!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations