Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide all PivotItems except for a specific Item
I really hope someone can help me with this. I am trying to make visible a
specified Pivot Item within a specific PivotField within a specific Pivot Table. I have been trying to adapt some code to hide all Pivot Items for all RowFields for all PivotTables but I haven't been very successful at it. Any help I could get would be greatly appreciated. Sub Shell() Run SpecPivotItemsVisible("PivotTable6", "Country", "US") End Sub Sub SpecPivotItemsVisible(PivotTbl As String, PivField As String, PivItem As String) 'hide all pivot items in all tables on sheet 'except last item Dim pt As PivotTable 'Pivot table Dim pf As PivotField 'Pivot field Dim pi As PivotItem ' specific pivot item Dim opi As PivotItem 'other pivot items pt = Worksheets("Hidden).PivotTables(PivotTbl).value pf = pt.PivotFields(PivField) pi = pf.PivotItems(PivItem) On Error Resume Next With Worksheets("Hidden").PivotTables(pf) RowField("gee").AutoSort xlManual For Each opi = not pi In PivotField("pf").PivotItems pi.Visible = False Next RowField("pf").AutoSort xlManual End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide all PivotItems except for a specific Item
The following code should work:
'================================= Sub Shell() SpecPivotItemsVisible "PivotTable6", "Country", "US" End Sub '============================== Sub SpecPivotItemsVisible(PivotTbl As String, PivField As String, PivItem As String) 'hide all pivot items in all tables on sheet 'except last item Dim pt As PivotTable 'Pivot table Dim pf As PivotField 'Pivot field Dim pi As PivotItem ' specific pivot item For Each pt In Worksheets("Hidden").PivotTables Debug.Print pt.Name Next pt Set pt = Worksheets("Hidden").PivotTables(PivotTbl) Set pf = pt.PivotFields(PivField) On Error Resume Next pt.ManualUpdate = True With pf .AutoSort xlManual, .SourceName .PivotItems(PivItem).Visible = True For Each pi In .PivotItems If pi.Name < PivItem Then pi.Visible = False End If Next .AutoSort xlAscending, .SourceName End With pt.ManualUpdate = False End Sub '===================================== DynamiteSkippy wrote: I really hope someone can help me with this. I am trying to make visible a specified Pivot Item within a specific PivotField within a specific Pivot Table. I have been trying to adapt some code to hide all Pivot Items for all RowFields for all PivotTables but I haven't been very successful at it. Any help I could get would be greatly appreciated. Sub Shell() Run SpecPivotItemsVisible("PivotTable6", "Country", "US") End Sub Sub SpecPivotItemsVisible(PivotTbl As String, PivField As String, PivItem As String) 'hide all pivot items in all tables on sheet 'except last item Dim pt As PivotTable 'Pivot table Dim pf As PivotField 'Pivot field Dim pi As PivotItem ' specific pivot item Dim opi As PivotItem 'other pivot items pt = Worksheets("Hidden).PivotTables(PivotTbl).value pf = pt.PivotFields(PivField) pi = pf.PivotItems(PivItem) On Error Resume Next With Worksheets("Hidden").PivotTables(pf) RowField("gee").AutoSort xlManual For Each opi = not pi In PivotField("pf").PivotItems pi.Visible = False Next RowField("pf").AutoSort xlManual End With End Sub -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Change PivotItems - Visible/Hide with VBA | Excel Worksheet Functions | |||
How to hide an item subtotal in the row field? | Excel Discussion (Misc queries) | |||
Count Specific Item in Specific Row | Excel Discussion (Misc queries) | |||
Excel 2003 Hide PivotItems | Excel Worksheet Functions | |||
Add PivotItems to PivotTable / Enabling - Disabling PivotItems | Excel Programming |