Identify Pivot Field using VBA

Question

I am trying to write a macro which automatically identifies which field is the data taken from in a pivot.

I have some columns with different level of org information dragged under one another in "Row Labels" to create a structure. Now I need to identify which row in the pivot is linked to which field.

I am a self-taught coder and am very new to advanced vba so need some help.

How my pivots look

I am trying to get the name of the field in the blue circle

Thank you!

It's not clear what you want to achieve. What does the code read? What do you expect the output to be? What is your code so far? — teylyn
– teylyn, Commented Nov 30, 2017 at 2:11
Use the macrorecorder to record you selecting and re-arranging that field. Then see which field is noted in the code. — QHarr
– QHarr, Commented Nov 30, 2017 at 3:13
@teylyn I am sorry I wasn't clear enough. What I am trying to do is to create a code which reads all the values which are not 'blank' in the pivot row by row and pastes them in a separate sheet and also specifies which level are they part. My output would be let's say I am at cell A15 which says "Finance" and i execute a command like "ActiveCell.FieldName". The output should be "Level 2". Does that make sense? — iPWN
– iPWN, Commented Nov 30, 2017 at 3:40
Why don't you just copy the Pivot to the other sheet, change Layout to Tabular, and filter out blank values? — jeffreyweir
– jeffreyweir, Commented Nov 30, 2017 at 3:46
@QHarr I am sorry, I guess I made that confusing. I am not trying to reorder. I am trying to identify which field does the row data belong to. — iPWN
– iPWN, Commented Nov 30, 2017 at 3:46

iPWN · Accepted Answer · 2017-11-30 04:03:22Z

1

Nevermind guys, It was simpler than I thought. I am just an idiot for not figuring it out earlier.

Anyhow, the solution is that "ActiveCell.PivotField" returns the Pivot row's field.

answered Nov 30, 2017 at 4:03

iPWN

133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Identify Pivot Field using VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related