Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi, i am new at QV and is my first post here.
i need to know if is possible to automatize collapsing and expanding of column in pivot table (the same to right click on the top of table and click on "expand all" or "collapse all".)
if i select level 3 of 7 the level under that should be collapse (i should see the button) and if i click on one of that should working as normal pivot table.
i need to have all level (i mean that i can't hide column as the value of a variable)
thanks in advance
Hi,
Try this Macro:
Hope it helps!!
hi,
i found this code but doesn't work.. (i am new and i have problem to adapt)
anyhow i understand that the code is for show or hide Expression... i need something to collapse or espande Dimension of pivot table.
if you can write the reference of the instruction to understand i thank you so much, i like to learn something new.
thanks
Hi
If you have button / text box for "3-7 Level", you can set action to run Macro with Macro Name - "ExpandAll".
Try below macro -
Sub ExpandAll
CollapseExpandPivot("CH01")
End Sub
Function CollapseExpandPivot (objectID)
Set chart = ActiveDocument.GetSheetObject(objectID)
set ch=chart.GetProperties
If ch.TableProperties.PivotAlwaysFullyExpanded =false then
ch.TableProperties.PivotAlwaysFullyExpanded=true
chart.SetProperties ch
Else
ch.TableProperties.PivotAlwaysFullyExpanded=false
chart.SetProperties ch
End If
End Function
Hoe this will resolve your issue.
--
Regards,
Prashant P Baste