Link to home
Start Free TrialLog in
Avatar of Rajnish_Sharma_ibm
Rajnish_Sharma_ibmFlag for India

asked on

count of pivot tables in a workbook

How to determine via vba the count of pivot tables (How many tables exist in a workbook ) in a workbook so that once can store there names in array(dynamic) and perform a series of operations
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Hello Rajnish,

the code below will count the pivot tables in all sheets of the active workbook.

cheers, teylyn
Sub CountPivotTables()
Dim ws As Worksheet
Dim i As Integer
i = 0
For Each ws In ActiveWorkbook.Worksheets
    For Each PivotTable In ws.PivotTables
        i = i + 1
    Next PivotTable
Next ws
Debug.Print i

End Sub

Open in new window

Dim i as Long
Dim  wb as workbook
Dim ws as worksheet

i = 0
Set wb as Workbookds("MyWorkbook.xls")
For each ws in wb.Worksheets
   i = i + ws.PivotTables.Count
Next ws
MsgBox "There are " & i & " Pivot Tables in your workbook"
cal, just learned something. Thanks, teylyn
Avatar of Chris Bottomley
Chris Bottomley
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@cal. precisely. I'm baby-stepping through VBA, as it is, spreading my "wisdom" where I can. LOL. Seeing the latest comment, I guess Chris has it nailed. Again. Cheers, Chris.
>>> I'm baby-stepping through VBA, as it is, spreading my "wisdom" where I can

And trouncing me at almost every opportunity ... with your level of detail :o)

Avatar of Rajnish_Sharma_ibm


Thanks for speedy and accurate solution!
>> with your level of detail

You can take the girl out of Germany, but you can't take Germany out of the girl! :-))

enjoy your points!