VBA: Truy quét và liệt kê tất cả Pivot Table bị lỗi trong File

Tình huống : Tôi mở một file Excel cũ file của đồng nghiệp, nhấn Refresh All và nhận về một thông báo lỗi vô hồn: "Reference is not valid". Khổ nỗi, file có 50 cái Pivot Table nằm rải rác ở 20 Sheets khác nhau. Tôi không biết cái nào chết, cái nào sống.

Thay vì ngồi click chuột từng cái để kiểm tra, Sub này sẽ ép từng Pivot Table phải Refresh. Cái nào "cãi lệnh" (báo lỗi), nó sẽ lập tức bị ghi tên vào danh sách "đen" ở một Sheet mới để bạn xử lý.

Sub CreateErrorPivotTableList()

Dim ws As Worksheet
Dim newSheet As Worksheet
Dim pt As PivotTable
Dim rowIndex As Long

' Tạo một sheet mới để lưu trữ danh sách
Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))


' Đặt tiêu đề cho danh sách
newSheet.Range("A1").Value = "Worksheet"
newSheet.Range("B1").Value = "PivotTable Name"
newSheet.Range("C1").Value = "Location"

rowIndex = 2 ' Vị trí hàng bắt đầu ghi dữ liệu
' Duyệt qua tất cả các worksheet trong tệp Excel
For Each ws In ThisWorkbook.Worksheets
' Duyệt qua tất cả các PivotTable trong worksheet
For Each pt In ws.PivotTables
On Error Resume Next
' Thử Refresh PivotTable
pt.RefreshTable
' Kiểm tra nếu có lỗi xảy ra
If Err.Number <> 0 Then
' Ghi tên worksheet vào danh sách
newSheet.Cells(rowIndex, 1).Value = ws.Name
' Ghi tên PivotTable vào danh sách
newSheet.Cells(rowIndex, 2).Value = pt.Name

' Ghi vị trí của PivotTable vào danh sách
newSheet.Cells(rowIndex, 3).Value = pt.TableRange1.Address
rowIndex = rowIndex + 1
End If

On Error GoTo 0
Next pt
Next ws
End Sub