Solved

count of pivot tables in a workbook

Posted on 2010-09-06
9
768 Views
Last Modified: 2013-11-26
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
0
Comment
Question by:Rajnish_Sharma_ibm
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
cal, just learned something. Thanks, teylyn
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
PivotTables.Count?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 125 total points
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@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
 
LVL 59

Expert Comment

by:Chris Bottomley
Comment Utility
>>> 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
 

Author Closing Comment

by:Rajnish_Sharma_ibm
Comment Utility
Thanks for speedy and accurate solution!
0
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
>> 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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now