Pivot table "Group and Show Details" vs. "SubTotals"

P

pgchop

In a Pivot table, When the "Group and Show Details" of a group is set to
"Show Details",
i.e. the details are visible, it is fairly easy to find out if the subtotal
has been set to
"none" or "Automatics":

For cdx = 1 To
Application.WorksheetFunction.Min(UBound(ActiveSheet.PivotTables("PivotTable1").RowRange.Cells.Value2, 2), 4)
If ActiveSheet.PivotTables("PivotTable1").RowRange.Cells(cdx) <>
"Article-no. + descr." Then
SubtotalName =
ActiveSheet.PivotTables("PivotTable1").RowRange.Cells(cdx)
ColNam = "'" & Replace(SubtotalName, "'", "''") & "'[All;Total]"
IsSubtotalVisible =
ActiveSheet.PivotTables("PivotTable1").PivotFields(SubtotalName).Subtotals(1)
If IsSubtotalVisible Then
ActiveSheet.PivotTables("PivotTable1").PivotSelect ColNam,
xlDataAndLabel, True
Selection.Interior.ColorIndex = RowBoundCol(cdx)
Selection.Interior.Pattern = xlSolid
Selection.WrapText = False
End If
End If
Next

But this code fail is the "Group and Show Details" of a group was set to
"Hide Details"
while the subtotal is still on "Automatic". This mean, the subtotal is here,
but not visible.

As you can see, I try here to color automatically the subtotal background.

How can I set IsSubtotalVisible to false if the "Group and Show Details" of
the group was set to "Hide Details" ???

Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

remove add pivot table fields 0
macro to turn pivot table field on and off 1
Looking to remove pivot fields from pivot table via macro 0
Programming a pivot 0
Pivot Table with no "Blanks" 1
Pivot Table - Add calculated field using field number (index) vs n 0
Update Pivot Table Filters based on a Cell Value in Excel 2007 0
Using macro to switch 'page' in a pivot table? 1

Top