VBA Làm mới Bảng Pivot | Tự động làm mới tất cả bảng tổng hợp bằng VBA
Bảng tổng hợp làm mới VBA trong Excel
Khi chúng tôi chèn một bảng tổng hợp trong trang tính, khi dữ liệu thay đổi, dữ liệu bảng tổng hợp không tự thay đổi, chúng tôi cần phải làm điều đó theo cách thủ công nhưng trong VBA có một câu lệnh để làm mới bảng tổng hợp đó là biểu thức .refreshtable , sử dụng điều này, chúng tôi có thể làm mới bảng tổng hợp bằng cách tham chiếu đến trang tính chứa nó hoặc chúng ta có thể tham chiếu đến toàn bộ bảng tổng hợp trong trang tính và làm mới tất cả chúng cùng một lúc.
Bảng tổng hợp rất quan trọng trong việc phân tích lượng dữ liệu khổng lồ. Nó giúp ích ngay từ việc phân tích, tóm tắt, cũng như giải thích dữ liệu hữu ích. Tuy nhiên, một trong những vấn đề với bảng tổng hợp này là nó sẽ không tự động được làm mới nếu có bất kỳ thay đổi nào trong dữ liệu nguồn, người dùng phải làm mới bảng tổng hợp bằng cách đi tới bảng tổng hợp cụ thể mỗi khi có thay đổi. Nhưng hãy tạm biệt quy trình thủ công vì ở đây chúng tôi có phương pháp để làm mới bảng tổng hợp ngay sau khi bạn thực hiện bất kỳ thay đổi nào trong bảng tổng hợp.
Làm thế nào để Tự động Làm mới Mã VBA Dữ liệu Bảng Pivot?
Thời gian duy nhất bảng tổng hợp cần được cập nhật là bất kỳ khi nào có bất kỳ thay đổi nào trong dữ liệu nguồn của bảng tổng hợp mà chúng tôi đang đề cập đến.
Ví dụ: hãy xem dữ liệu dưới đây và bảng tổng hợp.
Bây giờ tôi sẽ thay đổi các số trong dữ liệu nguồn tức là từ A1 thành B17.
Trong ô B9, tôi phải thay đổi giá trị từ 499 thành 1499 tức là dữ liệu tăng 1000 nhưng nếu bạn nhìn vào trục vẫn hiển thị kết quả là 4295 thay vì 5295. Tôi phải làm mới bảng tổng hợp của mình theo cách thủ công để cập nhật bảng tổng hợp.
Để khắc phục vấn đề này, chúng ta cần viết một mã macro excel đơn giản để làm mới bảng tổng hợp bất cứ khi nào có bất kỳ thay đổi nào trong dữ liệu nguồn.
Bạn có thể tải xuống Mẫu VBA Refresh Pivot Table Excel này tại đây - VBA Refresh Pivot Table Excel Template# 1 - Macro đơn giản để làm mới tất cả bảng
Bước 1: Thay đổi sự kiện của biểu dữ liệu
Chúng ta cần kích hoạt sự kiện thay đổi của biểu dữ liệu. Trong trình chỉnh sửa cơ bản trực quan, hãy nhấp đúp vào biểu dữ liệu.
Khi bạn nhấp đúp vào trang tính, hãy chọn “Trang tính” và chọn sự kiện là “Thay đổi”.
Bạn sẽ thấy một thủ tục phụ tự động được mở dưới dạng Worksheet_Change (ByVal Target As Range)
Bước 2: Sử dụng Đối tượng Trang tính
Tham khảo biểu dữ liệu bằng cách sử dụng đối tượng Worksheets.
Bước 3: Giới thiệu Bảng tổng hợp theo tên
Tham khảo tên bảng tổng hợp theo tên của bảng tổng hợp.
Bước 4: Sử dụng phương pháp làm mới bảng
Chọn phương thức là "Làm mới bảng".
Bây giờ, mã này sẽ làm mới bảng tổng hợp “PivotTable1” bất cứ khi nào có bất kỳ thay đổi nào trong bảng dữ liệu nguồn. Bạn có thể sử dụng mã dưới đây, bạn chỉ cần thay đổi tên bảng tổng hợp.
Mã:
Private Sub Worksheet_Change (ByVal Target As Range) Trang tính ("Data Sheet"). PivotTables ("PivotTable1"). RefreshTable End Sub
# 2 - Làm mới tất cả các bảng tổng hợp của cùng một trang tính
Nếu bạn có nhiều bảng tổng hợp trong cùng một trang tính, bạn có thể làm mới tất cả các bảng tổng hợp chỉ bằng một cú nhấp chuột. Sử dụng mã dưới đây để làm mới tất cả các bảng tổng hợp trong trang tính.
Mã:
Sub Refresh_Pivot_Tables_Example1 () Worksheets ("Data Sheet"). Chọn Với ActiveSheet .PivotTables ("Table1"). RefreshTable .PivotTables ("Table2"). RefreshTable .PivotTables ("Table3"). RefreshTable .PivotTables ("Table4"). RefreshTable .PivotTables ("Table5"). RefreshTable End With End Sub
Bạn cần thay đổi tên trang tính và tên bảng tổng hợp theo chi tiết trang tính của bạn.
# 3 - Làm mới tất cả các bảng trong Workbook
Rất ít khả năng chúng ta có tất cả các bảng tổng hợp trên cùng một trang tính. Thông thường, đối với mỗi báo cáo, chúng tôi cố gắng thêm các bảng tổng hợp riêng biệt trong các trang tính riêng biệt. Trong những trường hợp này, chúng tôi không thể tiếp tục viết mã cho mỗi bảng tổng hợp được làm mới.
Vì vậy, những gì chúng ta có thể làm là với một mã duy nhất bằng cách sử dụng các vòng lặp, chúng ta có thể lặp qua tất cả các bảng tổng hợp trên sổ làm việc và làm mới chúng bằng một lần nhấp vào nút.
Đoạn mã dưới đây sẽ lặp qua từng bảng tổng hợp và làm mới chúng.
Mã 1:
Sub Refresh_Pivot_Tables_Example2 () Dim PT dưới dạng PivotTable cho mỗi PT trong ActiveWorkbook.PivotTables PT.RefreshTable PT tiếp theo PT End Sub
Mã 2:
Sub Refresh_Pivot_Tables_Example3 () Dim PC làm PivotCache cho mỗi PC trong ActiveWorkbook.PivotCaches PC.Refresh Next PT End Sub
Cả hai mã sẽ thực hiện việc làm mới bảng tổng hợp.
Nếu bạn muốn bảng tổng hợp được làm mới ngay khi có bất kỳ thay đổi nào trong biểu dữ liệu của trang xoay vòng, bạn cần sao chép và dán các mã trên vào sự kiện Thay đổi trang tính trong sổ làm việc đó.
# 4 - Tránh thời gian tải bằng cách sử dụng sự kiện hủy kích hoạt trang tính
Khi chúng tôi sử dụng sự kiện “Thay đổi trang tính”, sự kiện sẽ tiếp tục làm mới ngay cả khi không có thay đổi nào trong nguồn dữ liệu nhưng nếu có bất kỳ thay đổi nào xảy ra trong trang tính.
Ngay cả khi bạn nhập một dấu chấm duy nhất trong trang tính, nó sẽ cố gắng làm mới bảng tổng hợp. Vì vậy, để tránh điều này, chúng ta có thể sử dụng phương pháp “Hủy kích hoạt trang tính” thay vì phương pháp “Thay đổi trang tính”.
Hủy kích hoạt cập nhật sự kiện trên bảng tổng hợp khi di chuyển từ trang tính này sang trang tính khác.
