CODEWITHSUNDEEP
excelvbacountpivot-table
ChrisB
ChrisB

Reputation: 3205

How can I count the number of pivot tables in an Excel workbook with VBA?

How can I use VBA to count the total number of pivot tables in a workbook?

Upvotes: 0

Views: 3065

Answers (1)

ChrisB
ChrisB

Reputation: 3205

I couldn't find an answer on StackOverflow so I wanted to share this. There is no native property that counts all pivot tables but there is a Worksheet.PivotTables.Count property. Loop through each sheet in a workbook and keep a running count like this:

Public Function CountPivotsInWorkbook(ByVal target As Workbook) As Long

    Dim tmpCount As Long
    Dim iWs As Excel.Worksheet

    For Each iWs In target.Worksheets
        tmpCount = tmpCount + iWs.PivotTables.Count
    Next iWs

    CountPivotsInWorkbook = tmpCount
End Function

Call the function like this:

Sub test()
    MsgBox CountPivotsInWorkbook(ActiveWorkbook)
End Sub

Upvotes: 3

Related Questions