Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Show All Pivot Table Items
I have a pivot table and a user form that allows the user
rearrange the pivot table by just clicking a button. The problem I'm having is when they hide items from a field then rearrange the table, those items remain hidden. Is there a code I can write that checks the "(Show All)" option on a field before my other code runs to alter the pivot table? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Show All Pivot Table Items
The following code will show all items for all visible fields:
'============================== Sub PivotShowItemAllVisible() 'For version 2000 -- show all items in field 'sort is set to Manual to prevent errors, e.g. 'unable to set Visible Property of PivotItem class Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Application.ScreenUpdating = False Application.DisplayAlerts = False On Error Resume Next For Each pt In ActiveSheet.PivotTables For Each pf In pt.VisibleFields pf.AutoSort xlManual, pf.SourceName For Each pi In pf.PivotItems If pi.Visible < True Then pi.Visible = True End If Next pi pf.AutoSort xlAscending, pf.SourceName Next pf Next pt Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub '===================================== Chris wrote: I have a pivot table and a user form that allows the user rearrange the pivot table by just clicking a button. The problem I'm having is when they hide items from a field then rearrange the table, those items remain hidden. Is there a code I can write that checks the "(Show All)" option on a field before my other code runs to alter the pivot table? -- 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 table - show items with no data | Excel Discussion (Misc queries) | |||
Show Pivot Table Items That Have No Data | Excel Discussion (Misc queries) | |||
Using Code to show all pivot items | Excel Discussion (Misc queries) | |||
Pivot table-show all grouped items | Excel Discussion (Misc queries) | |||
Pivot Table - show items with no data | Excel Discussion (Misc queries) |