CODEWITHSUNDEEP
excelvbapivot-table
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

How to get pivot item index, knowing its name

How to get in VBA pivot item index knowing pivot item's name?

I would like to get the reverse of this:

ActiveSheet.PivotTables(1).PivotFields("MyFiledName").PivotItems(10).Name

Knowing that the item name is "Something", I want to get the index:

.PivotItem(THISNUMBER).Name

Update. Following the answer of Marco Getrost I end up with error. It can be reproduced using the example file. I keep getting "Error 2042". The only case I do not get an error is when I select the item on the PageFilter as the only item (no Multiple Selection enabled) - in that case I always get position number 1.

In the example file I want to get the position number of item with Name="6" which obviously is 7.

Upvotes: 1

Views: 5516

Answers (1)

MGP
MGP

Reputation: 2551

Try this:

Sub test()

Dim pvItem As PivotItem
Dim c as Long

For Each pvItem in ActiveSheet.PivotTables(1).PivotFields("MyFiledName").PivotItems

c = c+1
If pvItem.Name = "Something" Then Exit For

Next pvItem

'THISNUMBER = c

End Sub

EDIT: After checking the official documentation (which is really helpful), the answer is yes, and this is how:

THISNUMBER = ActiveSheet.PivotTables(1).PivotFields("MyFiledName").PivotItems("Something").Position

Upvotes: 2

Related Questions