Pivot Table Inner Field

[nc]

Can information regarding the items selected in the
dropdown list for suppressed inner fields be obtained
through VB/formula. I would like to use that info in
another formula.

Thanks.

 

[Debra Dalgleish]

The following code creates a list of hidden items in a pivotfield named
"Rep":

'=================================
Sub ListHiddenItems()
Dim ItemCollection As New Collection
Dim wsL As Worksheet
Dim i As Long
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set wsL = Worksheets("Lists")
Set pt = Sheets("Pivot").PivotTables(1)
Set pf = pt.PivotFields("Rep")
i = 1
wsL.Cells.ClearContents
On Error Resume Next
For Each pi In pf.PivotItems
If pi.Visible = False Then
wsL.Cells(i, 1).Value = pi.Name
i = i + 1
End If
Next pi
On Error GoTo 0

'name the range of pivot items
wsL.Range("A1").CurrentRegion.Name = "HiddenItems"

End Sub
'===========================

 

[nc]

Thanks a lot Debra.

Can I attach this macro to an event that automatically
runs it when I select the items of this field.

 

[Debra Dalgleish]

You can use the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
ListHiddenItems
End Sub

 

[Guest]

Thanks again Debra

Can you please recommend a good book for learning VBA for Excel

 

[Debra Dalgleish]

There's a list here:

http://www.contextures.com/xlbooks.html

As an intro, I like:

Excel 2003 Power Programming with VBA ; John Walkenbach;
John Wiley & Sons; ISBN: 0-764540726 ; $49.99
Microsoft Excel 2002 Visual Basic for Applications Step by Step ;
Reed Jacobson; Microsoft Press; ISBN 0735613591 US$39.99

and for reference:

Excel 2002 VBA Programmers Reference; John Green, et al;
Wrox Press Inc; ISBN 1861005709; US$39.99

 

[Guest]

Debra

Thanks for the list of books.

I tried the pivot table event you mailed me, it does not seem to work with Excel 2000? Any ideas why?

 

[Debra Dalgleish]

In Excel 2000, you could use the Worksheet_Calculate event instead:

Private Sub Worksheet_Calculate()
ListHiddenItems
End Sub

 

[Guest]

Thanks loads Debra, you are a life saver

Have you seen my post re: Inner field/grouping problem? Any ideas?