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? |
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 |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com