Q:

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?

A:

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

