Cách tự động làm mới bảng Pivot bằng VBA Excel
Như chúng ta đã biết, bất cứ khi nào chúng ta thực hiện thay đổi trong dữ liệu nguồn của bảng tổng hợp, thì dữ liệu đó không phản ánh ngay lập tức trong bảng tổng hợp. Chúng tôi cần làm mới bảng tổng hợp để xem các thay đổi. Và nếu bạn gửi một tệp cập nhật mà không làm mới bảng tổng hợp, bạn có thể cảm thấy bối rối. Vì vậy, trong bài viết này, chúng ta sẽ tìm hiểu cách tự động làm mới bảng tổng hợp bằng VBA. Cách này dễ hơn bạn tưởng tượng.
Đây là cú pháp đơn giản để tự động làm mới bảng tổng hợp trong sổ làm việc.
'Code in Source Data Sheet Object
Private Sub Worksheet_Deactivate()
sheetname_of_pivot_table.PivotTables("pivot_table_name").PivotCache.Refresh
End Sub
Pivot Caches là gì? Mỗi bảng tổng hợp đều lưu trữ dữ liệu trong bộ đệm tổng hợp. Đây là lý do tại sao pivot có thể hiển thị dữ liệu trước đó. Khi chúng tôi làm mới bảng tổng hợp, nó sẽ cập nhật bộ đệm với dữ liệu nguồn mới để phản ánh những thay đổi trên bảng tổng hợp.
Ở đây chúng ta sẽ sử dụng Sự kiện Worksheet_SelectionChange. Điều này sẽ làm cho mã chạy bất cứ khi nào chúng ta chuyển từ trang dữ liệu nguồn sang trang tính khác.
Tôi sẽ giải thích sau tại sao tôi sử dụng sự kiện này.
Ở đây, tôi có dữ liệu nguồn trong sheet2 và bảng tổng hợp trong sheet1.
Mở VBE bằng phím CTRL + F11. Trong trình khám phá dự án, bạn có thể thấy ba đối tượng, Sheet1, Sheet2 và Workbook.
Vì Sheet2 chứa dữ liệu nguồn, hãy nhấp đúp vào đối tượng sheet2.
Bây giờ bạn có thể thấy hai trình đơn thả xuống ở đầu vùng mã. Từ menu thả xuống đầu tiên, hãy chọn trang tính. Và từ menu thả xuống thứ hai, hãy chọn Hủy kích hoạt. Thao tác này sẽ chèn một tên phụ rỗng Worksheet_Deactivate. Mã của chúng tôi sẽ được viết trong phụ này. Bất kỳ dòng nào được viết trong phụ này, sẽ được thực thi ngay khi người dùng chuyển từ trang này sang bất kỳ trang nào khác.
Trên sheet1, tôi có hai bảng tổng hợp. Tôi chỉ muốn làm mới một bảng tổng hợp. Để làm được điều đó, tôi cần biết tên của bảng tổng hợp. Để biết tên của bất kỳ bảng tổng hợp nào, hãy chọn bất kỳ ô nào trong bảng tổng hợp đó, hãy chuyển đến tab phân tích bảng tổng hợp. Ở phía bên trái, bạn sẽ thấy tên của bảng tổng hợp. Bạn cũng có thể thay đổi tên của bảng tổng hợp tại đây.
Bây giờ chúng ta biết tên của bảng tổng hợp, chúng ta có thể viết một dòng đơn giản để làm mới bảng tổng hợp.
Private Sub Worksheet_Deactivate()
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Và nó được thực hiện.
Bây giờ bất cứ khi nào bạn chuyển từ dữ liệu nguồn, mã vba này sẽ chạy để làm mới bảng tổng hợp1. Như bạn có thể thấy trong gif bên dưới.
Làm thế nào để làm mới tất cả bảng tổng hợp trong sổ làm việc? Trong ví dụ trên, chúng tôi chỉ muốn làm mới một bảng tổng hợp cụ thể. Nhưng nếu bạn muốn làm mới tất cả các bảng tổng hợp trong sổ làm việc, bạn chỉ cần thực hiện các thay đổi nhỏ đối với mã của mình.
Private Sub Worksheet_Deactivate()
'Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
For Each pc In ThisWorkbook.PivotCaches
pc.Refresh
Next pc
End Sub
Tại sao sử dụng Worksheet_Deactivate Event?
Nếu bạn muốn làm mới bảng tổng hợp ngay khi có bất kỳ thay đổi nào trong dữ liệu nguồn, bạn nên sử dụng sự kiện Worksheet_Change. Nhưng tôi không khuyến khích nó. Nó sẽ làm cho sổ làm việc của bạn chạy mã mỗi khi bạn thực hiện bất kỳ thay đổi nào trong trang tính. Bạn có thể phải thực hiện hàng trăm thay đổi trước khi muốn xem kết quả. Nhưng excel sẽ làm mới bảng tổng hợp mỗi lần thay đổi. Điều này sẽ dẫn đến lãng phí thời gian xử lý và nguồn lực. Vì vậy, nếu bạn có bảng tổng hợp và dữ liệu trong các trang tính khác nhau, tốt hơn nên sử dụng Sự kiện hủy kích hoạt trang tính. Nó cho phép bạn hoàn thiện công việc của mình. Khi bạn chuyển sang trang tính bảng tổng hợp để xem các thay đổi, nó sẽ sửa đổi các thay đổi.
Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
Làm thế nào để Làm mới Mọi thứ trong Sổ làm việc Khi Một Thay đổi được Thực hiện trong Dữ liệu Nguồn? Nếu bạn muốn làm mới mọi thứ trên sổ làm việc (biểu đồ, bảng tổng hợp, công thức, v.v.), bạn có thể sử dụng lệnh ThisWorkbook.RefreshAll.
Private Sub Worksheet_Change(ByVal Target As Range) ThisWorkbook.RefreshAll End Sub
Vì vậy, bạn ơi, đây là cách bạn có thể tự động làm mới bảng tổng hợp trong Excel. Tôi hy vọng tôi đã giải thích đủ và bài viết này phục vụ bạn tốt. Nếu bạn có bất kỳ câu hỏi nào liên quan đến chủ đề này, bạn có thể hỏi tôi trong phần bình luận bên dưới.
Bài viết liên quan:
Cách cập nhật động phạm vi nguồn dữ liệu của bảng tổng hợp trong Excel: Để thay đổi động phạm vi dữ liệu nguồn của bảng tổng hợp, chúng tôi sử dụng bộ đệm tổng hợp. Vài dòng này có thể cập nhật động bất kỳ bảng tổng hợp nào bằng cách thay đổi phạm vi dữ liệu nguồn. Trong VBA sử dụng các đối tượng bảng tổng hợp như hình dưới đây …
Bài viết phổ biến:
Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.