How to find pivot table field values of active cell?

**Iain21** · 12-02-2010, 06:20 AM

In a pivot table, how do I pass the selected cell field values (ie the values of row & column data for the selected cell) to VBA? I just can't find the correct syntax... I think it's something like

ActiveCell.PivotField("fieldname").value but that doesn't work.

(In Access, I can use Me.PivotTable.ActiveObject.Cell.Recordset.Fields("fieldname").Value)

Have looked through VBA's locals window without success.

Thanks in advance.

**romperstomper** · 12-02-2010, 07:13 AM

Could you give a specific example? I'm not quite following what it is you want returned.

**Iain21** · 12-02-2010, 08:07 AM

Example in attached image; if I click on cell L6, I want to find the Supplier, ItemID and ItemType values corresponding to that cell, in VBA.

**romperstomper** · 12-02-2010, 08:23 AM

In what format do you want them returned? You basically want the Name property of the Row or ColumnItems:

Please Login or Register  to view this content.

for the supplier for example.

**watersev** · 12-02-2010, 08:27 AM

Originally Posted by Iain21

Example in attached image; if I click on cell L6, I want to find the Supplier, ItemID and ItemType values corresponding to that cell, in VBA.

why not to add respective columns to pivot table?

**Iain21** · 12-02-2010, 08:43 AM

romperstomper - thank you very much, simple when you know how! I see the ColumnItems/RowItems number will have to be "hard coded" into the VBA, but this solution will work fine for me as I don't intend to change the layout of the pivot table. I thought I could reference the values using its name - eg "ItemID" as I could in Access, but perhaps this is not possible. In any case it'll work for me, so thanks.

**romperstomper** · 12-02-2010, 08:56 AM

You could loop through each one and check its parent field name if you wanted.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**vsm** · 09-14-2012, 09:34 AM

Hi this query seems to be un answered or the solution provided is not working. Can someone help on this please?

**vsm** · 09-14-2012, 09:36 AM

This remains unanswered or the solution provided does not work. Can someone help here

**jeffreybrown** · 09-14-2012, 09:39 AM

Hi vsm & Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.