Test if Pivot Item exists in a Pivot Field

O

OscarJr15

New Member
Joined
Mar 7, 2007
Messages
49
Is there a way I can check to see if a pivot item exists in a particular pivot field by using vba?

I have a pivot table that has all of the sales data for the sales reps in my company. I can automatically change the name of the rep in the pivot table by changing the value in an adjacent cell by using a worksheet change event. I ran into a problem when a new sales rep started and he had not made a sale yet. I have been trying to find a way to test to see if the pivot item, the sales rep name, exists before the pivot table updates. If the pivot item does not exist the pivot table should hide all of the values in the Pivot Field.

Any ideas? Thanks
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Code: 
Sub test()

  MsgBox bFieldItemExists("name to check")

End Sub

Function bFieldItemExists(strName As String) As Boolean
  
  Dim strTemp As String
  On Error Resume Next
  strTemp = ActiveSheet.PivotTables(1).PivotFields("sales rep").PivotItems(strName)
  If Err = 0 Then bFieldItemExists = True Else bFieldItemExists = False

End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Mutiple pivot table dates dropdown
Replies
0
Views
123
mukesh a
M
H
  • Question
Make an slicer in vba excel automatically
Replies
1
Views
95
Domenic
D
S
  • Question
Repeating a pivot table many times and changing only one column -- are there more efficient approaches?
Replies
0
Views
170
sschaefer
S
R
  • Question
Default Cell location AFTER slicer selection.
Replies
0
Views
102
RobertCotton
R
A
  • Solved
Pivot Table Slicers mystery!!!
Replies
9
Views
851
adambc
A

Forum statistics

Threads
1,215,006
Messages
6,122,666
Members
449,091
Latest member
peppernaut

Share this page

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