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.

Solution

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

Comments

[Rachel Hettinger]

This code just doesn't work and it doesn't return any information. Also, the declarations shouldn't be done over and over within the loops.

[dash]

Missed the copy and paste error (thanks!); otherwise it worked fine - in fact, apart from the pivotTableCount check, our code is the same in terms of loops. I wanted to demonstrate to the OP two things; the worksheet loop and the pivot tables collection, as well as showing some typical properties. I'd expect someone to be willing to f8 through the code to see how each bit works. Your point about declaring variables in loops is an interesting one though - it can really depend. I prefer to declare variables close to where I use them for readability AND scope, especially in larger modules.