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
Rajnish_Sharma_ibmPrinciple ConsultantAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Software Quality Lead EngineerCommented:
The following will return both the sheet names and pivot table names so you can address them appropriately as:

activeworkbook.sheets(arrPivots(arrIndex, 0))(.pivottables(arrPivots(arrIndex, 0))

Where arrindex is the index number within the array for the tables ... possibly via a loop.

Chris
Sub pivArr()
Dim arrPivots() As String
Dim arrIndex As Integer
Dim piv As Variant
Dim cnt As Integer
Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        cnt = cnt + ws.PivotTables.Count
    Next
    arrIndex = -1
    For Each ws In ThisWorkbook.Worksheets
        For Each piv In ws.PivotTables
            arrIndex = arrIndex + 1
            ReDim Preserve arrPivots(0 To cnt - 1, 1)
            arrPivots(arrIndex, 0) = ws.Name
            arrPivots(arrIndex, 1) = piv.Name
        Next
    Next
End Sub

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

0
 
calacucciaCommented:
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"
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
cal, just learned something. Thanks, teylyn
0
 
calacucciaCommented:
PivotTables.Count?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@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.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
>>> 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)

Chris
0
 
Rajnish_Sharma_ibmPrinciple ConsultantAuthor Commented:
Thanks for speedy and accurate solution!
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>> 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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.