Select ∑ values in PivotTable VBA

[bcn1988]

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!

[JosephP]

perhaps

Please Login or Register  to view this content.

but you shouldn't need to actually select it

[bcn1988]

Awesome! Works like a charm.

Thanks JP!

[bcn1988]

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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?

[JosephP]

Please Login or Register  to view this content.

for instance

[bcn1988]

Hey JP,

the code failed at this line:

Please Login or Register  to view this content.

[JosephP]

my bad-remove the word Selection

[bcn1988]

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.

[JosephP]

shoot-didn't see you were using relative addressing so you have to select

Please Login or Register  to view this content.

[bcn1988]

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?

Please Login or Register  to view this content.

[JosephP]

I can't see anything obvious-perhaps you could attach a sample workbook?

note: I am snowed at work at the moment so will not be around much for the next week or so

[bcn1988]

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!!

[bcn1988]

Hey JP,

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