Getting a pivot table value from VBA Code

BenW71

New Member
Joined
Apr 19, 2018
Messages
28
I need to get the value the "Division" from a row in a pivot table in VBA. There are 2 use cases:
1) If the division column is not displayed - i don't know if this is possible or not...
2) If the column IS displayed but it has that summarized view (i don't want to force the user into classic view), such as below where the "division" shows as headers and then a project field is underneath it. In this case there are two rows in the table for project 3, one would have division of "IT" and another would have division of "Digital". When going down the rows i would like the correct values for each row.

In the display i'm using for #2 above, here is what the table shows like:
1706035074621.png

The values in each row i would like to get would be IT (from summary row) , IT (fromproj 1 row), IT (from proj 2 row), IT (from proj 3 row)... Digital (from digital summary row), Digital (from the proj 3 row under digital) last two would be EIS...

Note: If i DID change to classic view (which i don't want to do), it shows the data like below. The division is there but just blank as it's not repeating
1706034996976.png


I am wondering if there is a way to use GETPIVOTDATA with field name "division" and refer to the row number you're on...

Thanks!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Threads
1,223,886
Messages
6,175,191
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top