CODEWITHSUNDEEP
excelpivot-tablevba
LWC
LWC

Reputation: 1176

Looping through all pivot tables that use a certain pivotcache

If a workbook has multiple worksheets with multiple pivot tables with multiple pivotcaches, how can I loop through only pivot tables that use a specific pivotcache? For example, if I want to change a field only those pivot tables have.

I can obviously loop through every single pivot table in the workbook and check its SourceData, but I wonder if there's something like:

For Each pt In ThisWorkbook.PivotCaches("this").pivottables_list
Next

Upvotes: 0

Views: 1224

Answers (1)

jeffreyweir
jeffreyweir

Reputation: 4824

There's strangely no direct way to get this from the PivotCache. Instead, you need to loop through every PT in the workbook, and see if it's PivotCache is the same as the one you're interested in, like this:

Option Explicit

Sub Loop_PivotCache(ptExample As PivotTable)
Dim pt As PivotTable
Dim pc As PivotCache

Set pc = ptExample.PivotCache

For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        If pt.PivotCache.Index = pc.Index Then
            Debug.Print pt.Name
        End If
    Next pt
Next wks
End Sub

Call that sub like so:

Sub Caller()
Loop_PivotCache Worksheets("SomeWorksheet").PivotTables("SomePivotTable")
End Sub

Question: What are you wanting to actually do to each PivotTable? Depending on what you're trying to do, there are some things you probably need to know from an efficiency perspective.

Upvotes: 3

Related Questions