Excel VBA Segarkan Tabel Pivot

Saat kita memasukkan tabel pivot di sheet, setelah data berubah, data tabel pivot tidak berubah sendiri kita perlu melakukannya secara manual tetapi di VBA ada pernyataan untuk menyegarkan tabel pivot yaitu ekspresi.refreshtable , dengan menggunakan ini kita dapat menyegarkan tabel pivot dengan merujuk ke lembar kerja yang memuatnya atau kita dapat merujuk ke seluruh tabel pivot di lembar kerja dan menyegarkan semuanya sekaligus.

Tabel pivot sangat penting dalam menganalisis data dalam jumlah besar. Ini membantu langsung dari menganalisis, meringkas, serta membuat interpretasi data yang berguna. Namun salah satu masalah dengan tabel pivot ini adalah tidak otomatis diperbarui jika ada perubahan dalam data sumber, pengguna harus menyegarkan tabel pivot dengan membuka tabel pivot tertentu setiap kali ada perubahan. Tapi ucapkan selamat tinggal pada proses manual karena di sini kami memiliki metode untuk menyegarkan tabel pivot segera setelah Anda membuat perubahan di tabel pivot.

Bagaimana Cara Menyegarkan Kode VBA Data Tabel Pivot Secara Otomatis?

Satu-satunya saat tabel pivot perlu diperbarui adalah setiap kali ada perubahan dalam data sumber tabel pivot yang kami maksud.

Misalnya, lihat data di bawah ini dan tabel pivot.

Sekarang saya akan mengubah angka di data sumber yaitu dari A1 ke B17.

Di sel B9 saya harus mengubah nilai dari 499 menjadi 1499 yaitu 1000 peningkatan dalam data tetapi jika Anda melihat pivot masih menunjukkan hasil sebagai 4295, bukan 5295. Saya harus menyegarkan tabel pivot secara manual untuk memperbarui tabel pivot.

Untuk mengatasi masalah ini kita perlu menulis kode makro excel sederhana untuk menyegarkan tabel pivot setiap kali ada perubahan dalam data sumber.

Anda dapat mengunduh Template Excel Tabel Pivot Refresh VBA ini di sini - Template Excel Tabel Pivot Refresh VBA

# 1 - Makro Sederhana untuk Menyegarkan Semua Tabel

Langkah 1: Ubah Acara Lembar Data

Kita perlu memicu peristiwa perubahan pada lembar data. Di editor visual basic, klik dua kali pada lembar data.

Setelah Anda mengklik dua kali pada lembar, pilih "Lembar Kerja" dan pilih acara sebagai "Ubah".

Anda akan melihat sub prosedur otomatis dibuka sebagai Worksheet_Change (ByVal Target As Range)

Langkah 2: Gunakan Objek Lembar Kerja

Lihat lembar data dengan menggunakan objek Lembar Kerja.

Langkah 3: Lihat Tabel Pivot berdasarkan Nama

Lihat nama tabel pivot dengan nama tabel pivot.

Langkah 4: Gunakan Metode Tabel Refresh

Pilih metode sebagai "Segarkan Tabel".

Sekarang, kode ini akan menyegarkan tabel pivot "PivotTable1" setiap kali ada perubahan dalam lembar data sumber. Anda dapat menggunakan kode di bawah ini, Anda hanya perlu mengubah nama tabel pivot.

Kode:

 Private Sub Worksheet_Change (ByVal Target As Range) Worksheet ("Data Sheet"). PivotTable ("PivotTable1"). RefreshTable End Sub

# 2 - Segarkan Semua Tabel Pivot dari Lembar Kerja yang Sama

Jika Anda memiliki banyak tabel pivot di lembar kerja yang sama, Anda dapat menyegarkan semua tabel pivot dalam satu klik saja. Gunakan kode di bawah ini untuk menyegarkan semua tabel pivot di lembar.

Kode:

 Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Pilih Dengan ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub

Anda perlu mengubah nama lembar kerja dan nama tabel pivot sesuai detail lembar kerja Anda.

# 3 - Segarkan Semua Tabel di Buku Kerja

Sangat tidak mungkin kita memiliki semua tabel pivot pada lembar kerja yang sama. Biasanya, untuk setiap laporan, kami mencoba menambahkan tabel pivot terpisah di lembar terpisah. Dalam kasus ini, kami tidak dapat terus menulis kode untuk setiap tabel pivot untuk disegarkan.

Jadi, yang dapat kita lakukan adalah dengan satu kode menggunakan loop, kita dapat mengulang melalui semua tabel pivot di buku kerja dan menyegarkannya dengan satu klik tombol.

Kode di bawah ini akan mengulang setiap tabel pivot dan menyegarkannya.

Kode 1:

 Sub Refresh_Pivot_Tables_Example2 () Redupkan PT Sebagai PivotTable Untuk Setiap PT Di ActiveWorkbook.PivotTables PT.RefreshTable Berikutnya PT End Sub

Kode 2:

 Sub Refresh_Pivot_Tables_Example3 () Redupkan PC Sebagai PivotCache Untuk Setiap PC Di ActiveWorkbook.PivotCaches PC. Segarkan Berikutnya PT End Sub

Kedua kode tersebut akan melakukan penyegaran tabel pivot.

Jika Anda ingin tabel pivot direfresh segera setelah ada perubahan dalam lembar data lembar pivot, Anda perlu menyalin dan menempelkan kode di atas ke acara Perubahan Lembar Kerja di buku kerja itu.

# 4 - Hindari Memuat Waktu dengan menggunakan Worksheet Deactivate Event

Saat kami menggunakan acara "Perubahan Lembar Kerja", acara terus diperbarui meskipun tidak ada perubahan dalam sumber data tetapi jika ada perubahan yang terjadi di lembar kerja.

Bahkan jika Anda memasukkan satu titik di lembar kerja, ia mencoba menyegarkan tabel pivot. Jadi untuk menghindari hal ini kita dapat menggunakan metode "Worksheet Deactivate" daripada metode "Worksheet Change".