Select ∑ values in PivotTable VBA

Hello!

I have a pivot table that will vary with number of columns as well as rows. I need some code that will select all of the ∑ values in the pivot table. I do not want the row labels or the column labels selected. ONLY the ∑ values so that i can apply conditional formatting. Please help!

Awesome! Works like a charm.

Thanks JP!

Hey JP,

One thing I need to alter for this line of code. The reason I needed to select the sum of values in the pivot table is to apply conditional formatting. The code I have to do this is as follows:

Sub ConditionalFormatPT2()
    ActiveSheet.PivotTables("ItemList").DataBodyRange.Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(C10))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With

One thing that I need to change, is this is selecting my "Grand Total" at the bottom of the pivot table. However, I do not want to Conditional Format this line. How can I Offset this? I tried changing the DataBodyRange select statement to:

ActiveSheet.PivotTables("ItemList").DataBodyRange.Offset(-1,0).Select

This did work to not select the "Grand Total" row, but it just shifted the selected range up 1 which included the pivot table headers. I'd like to select the databody range minus the grand total row. Any advice?

Hey JP,

the code failed at this line:

      .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Taking the selection out the code executes all the way through. However, It is not applying the conditional formatting properly like the beginning code I provided.

JP,

This works well for a few of my PivotTables. I am trying to apply this code to multiple tables. However, there are two formats of the pivottables in regards to what is in the Row Labels. The first code I have below (ConditionalFormatPT) is for a pivot table that only has "1 row label", hence why the formula "=LEN(TRIM(B10))=0" has B10 as the starting reference. In the second code I have below (ConditionalFormatPT2) is for a pivot table that has "2 row labels", hence why I have to push out the formula by one column since the 2nd row label is in column B and the DataBodyRange starts in C10. The ConditionalFormatPT works perfectly and applys the conditional formatting. However, for some strange reason, ConditonalFormatPT2 does not apply the conditional formatting whatsoever. After running the code, I go into the conditional formatting manage rules and there is not even a rule applied to the pivot table. Any idea as to why ConditionalFormatPT2 procedure is running all the way through but not applying the rule?

Sub ConditionalFormatPT()
   Dim rFormat As Range
    With ActiveSheet.PivotTables("ItemList").DataBodyRange
       Set rFormat = .Resize(.Rows.Count - 1)
    End With
    With rFormat
       .Select
       .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(B10))=0"
       .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
  End With
End Sub

Sub ConditionalFormatPT2()
    Dim rFormat As Range
    With ActiveSheet.PivotTables("ItemList").DataBodyRange
       Set rFormat = .Resize(.Rows.Count - 1)
    End With
    With rFormat
       .Select
       .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(C10))=0"
       .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.499984740745262
    End With
  End With
End Sub

Unfortunately, I can't upload the workbook as it holds proprietary data. I am calling the ConditionalFormatPT2 procedure. The weird thing is, is that after calling the ConditionalFormatPT2, it doesn't apply the conditional formatting rule. But after the call statement and the VBA is done runnning, I go into the ConditionalFormatPT2 procedure and run this code individually, it applies the formatting rule. I am at a loss!!

Hey JP,

Pretty weird, but all I did was moved the call statement and it worked. CHEERS! Thanks for all the help!