VBA: Tự động liệt kê danh sách và nguồn dữ liệu của tất cả Pivot Table trong File

Tình huống cần dùng: Bạn tiếp nhận một file Excel khổng lồ từ người tiền nhiệm hoặc hệ thống, trong đó có hàng chục Pivot Table nằm ở nhiều Sheet khác nhau.

• Bạn không biết Pivot nào đang lấy dữ liệu từ vùng nào (có cái lấy từ Table, có cái lấy vùng cố định, có cái lấy từ file ngoài).

• Bạn sợ rằng khi cập nhật dữ liệu mới, một số Pivot sẽ bị sót do sai vùng tham chiếu (Source Range).

Thay vì phải click vào từng Pivot Table rồi vào Change Data Source để xem, Sub này sẽ tạo ra một Sheet "Mục lục" liệt kê chi tiết: Tên Pivot, nằm ở Sheet nào, địa chỉ vùng dữ liệu nguồn và vị trí của nó trong file.

Sub ListPivotTables()

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 = "Source Range"
newSheet.Range("D1").Value = "Table address "



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
' 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
' Chuyển đổi địa chỉ nguồn dữ liệu từ R1C1 sang A1
Dim sourceAddress As String
sourceAddress = pt.SourceData
newSheet.Cells(rowIndex, 3).Value = Application.ConvertFormula(sourceAddress, xlR1C1, xlA1)

' Lấy địa chỉ của PivotTable (dưới dạng A1:B10)
newSheet.Cells(rowIndex, 4).Value = pt.TableRange2.Address

rowIndex = rowIndex + 1
Next pt
Next ws

End Sub