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.

score 6 · Accepted Answer · answered Dec 15 '11 at 04:59

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

score 0 · Answer 2 · edited Aug 20 '14 at 18:59

0

Each worksheet should expose a PivotTables collection; you can loop through each worksheet looking for sheets that have a .PivotTables.Count > 0 and then loop through the PivotTables on that sheeet to find the one you are looking for:

Sub Test()

    Dim pTable As pivotTable

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        Dim pivotTableCount

        pivotTableCount = ws.PivotTables.Count

        If pivotTableCount > 0 Then

            For Each pTable In ws.PivotTables

                 Dim pivotTableName As String
                 pivotTableName = pTable.Name

                 Dim pivotTableTopLeftCell As String
                 pivotTableTopLeftCell = pTable.Location

            Next pTable 

        End If



    Next ws


End Sub

edited Aug 20 '14 at 18:59

NorthCat

9,643
16
47
50

answered Dec 14 '11 at 20:29

dash

89,546
4
51
71

1

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. – Rachel Hettinger Dec 15 '11 at 05:08
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. – dash Dec 15 '11 at 09:11

Locate excel 2007 Pivot table

2 Answers2