VBA: Pivot Table cell Row and Column reference

[GuruWannaB]

I believe I am overthinking this.

I have a pivot table, which by default provides a GRAND TOTAL row. I am trying to create a macro that references a cell in the GRAND TOTAL row and a pivot table column labeled "ACW". What would the cell selection statement look like? The actual cell row changes each time I change my filters....but the row name remains the same which is why I need to reference the name... I believe there is a simple answer - but I am drawing a blank.

Thanks in advance,

[JeteMc]

It would seem as if you could use an Index/Match formula to accomplish this.
Say that the row labels are in column A starting with row 3 and that there are 200 rows when no filters are applied.
Also let "ACW" be the column label in column D
Use the following to find the grand total of the "ACW" column: =INDEX(D3:D200,MATCH("Grand Total",A3:A200,0))
Let us know if you have any questions.

[GuruWannaB]

Thanks for the formula....however, I am having an issue in using this in a macro....to identify this cell in column D, with Grand Total in Column A, and changing the formatting....to..lets say a black background.

Thanks in advance,

GWB

[JeteMc]

I interpreted 'reference' to mean retrieve, as in retrieve the value of the 'ACW' column of the Grand Total row and display it in another cell. I don't have experience with VBA, however I did find this article that may be of some help.
https://www.clearpeaks.com/grand-tot...-pivot-tables/

[rorya]

Is ACW a field name, or an item within a field?

[GuruWannaB]

ACW is a field name

[GuruWannaB]

I actually found a work around. Since the ACW column is consistent - I used the following code to select the last cell in the column - which for my spreadsheet, is always the grand total row.

Please Login or Register  to view this content.