none
How to Identify Pivot Table? RRS feed

  • Question

  • Hello,

    i need to identify Pivot Tables that are situated in my workbook. I can do this only with pivot table name. But i want to find another way to do this. Because sometimes i need to rename table and all vba code must be modifyed.

    Thanks in advance for your help!

    Monday, July 23, 2012 10:48 AM

Answers

  • You can also refer to a pivot table by its index. In particular, if there is only one pivot table on a sheet named Sheet1, you can refer to it as

    Worksheets("Sheet1").PivotTables(1)

    If there are multiple pivot tables you can loop through them:

    Sub LoopPivots()
        Dim wsh As Worksheet
        Dim pvt As PivotTable
        Set wsh = Worksheets("Sheet1")
        For Each pvt In wsh.PivotTables
            Debug.Print pvt.Name & ": " & pvt.TableRange2.Address
        Next pvt
    End Sub

    The result will be a list like this in the Immediate window:

    PivotTable2: $E$19:$J$21
    PivotTable1: $H$3:$I$8

    You can manipulate the pivottables in the loop, of course.


    Regards, Hans Vogelaar

    • Proposed as answer by Rod Gill Monday, July 23, 2012 11:38 PM
    • Marked as answer by Vitali Kuntsevich Tuesday, July 24, 2012 5:06 AM
    Monday, July 23, 2012 11:59 AM