Locate excel 2007 Pivot table

Question

Can someone tell me how I can find all the pivot tables in a workbook (or sheet)? In some cases a pivot table might be hidden or hard to find in a very large excel sheet. If i could at least get the cell address or range of where the pivot table(s) are/is, that would be great.

Thanks.

Answer 1

This should work for you. It prints out the results to the Immediate window:

Sub FindPivotTables()
    Dim wst As Worksheet
    Dim pvt As PivotTable
    ' loop through all sheets and print name & address of all pivot tables
    For Each wst In ActiveWorkbook.Worksheets
        For Each pvt In wst.PivotTables
            Debug.Print wst.Name, pvt.TableRange2.Address, pvt.Name
        Next pvt
    Next wst
End Sub