Pivot table

[brindlogcool]

Hi,

Is there any way we can expand all the columns in the pivot table using trigger.

Ideally i would like to expand all the columns and export to excel.

Right now we have to expand each and every column through right click or have to select always fully expanded or use the macro. Other than these options is there any way i can expand all the column in one click.

[Jason_Michaelides]

Nope - afraid not. We are using a button that fires a macro to do it.

Hope this helpa,

Jason

[#3]

Hi Jason,

Could you pls share the macro?  I am trying to do exactly that but I do not know enough to code the macro by myself.

Thanks!

Marina

[Jason_Michaelides]

The code below sets the "always fully expand" property so you will most likely need a "collapse" button too to uncheck it. vChartToExpand is a variable with the ObjectID of the chart to expand/collapse.

Hope this helps,

Jason

Sub ExpandChart
'This macro fully expands the selected pivot table.
  On Error Resume Next

  Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
  Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
  Set gp = chart.GetProperties
  gp.TableProperties.PivotAlwaysFullyExpanded = TRUE 'not gp.TableProperties.PivotAlwaysFullyExpanded
  chart.SetProperties gp
End Sub

Sub CollapseChart
'This macro allowse collapsing of the selected pivot table.
  On Error Resume Next

  Set vChartToExpand = ActiveDocument.Variables("vChartToExpand")
  Set chart = ActiveDocument.GetSheetObject(vChartToExpand.GetContent.String)
  Set gp = chart.GetProperties
  gp.TableProperties.PivotAlwaysFullyExpanded = FALSE 'not gp.TableProperties.PivotAlwaysFullyExpanded
  chart.SetProperties gp
End Sub