Pivot Table pagefield returns wrong data when changed with vba

[kuraitori]

Hello All,

Okay, have a combobox populated with salespeople
you select one and click a "lookup" button. This changes the page fields of multiple pivottables to that salesperson.

here is the problem. On occasion, the data that is returned actually belongs to a different salesperson. Like if I specify Fred, the page field will be changed to Fred, but when I drill down and have a sheetx with sourcedate, the saleperson will turn out to be Susan.

wtf excel? I thought we had a deal!

Any idea what might be causing it?

[davegugg]

It'd be easier to figure out if you'd post a sample workbook, or at least your code.

[kuraitori]

Here is the code, not sure I can replicate the behaviour in a sample notebook

Please Login or Register  to view this content.

so what my issue is, is that sometimes, I'll put "John Smith" in the combobox (Sheet8.CL_SalesPersonLookup.Value) and the pivottable page field will say "John Smith" but the data will reflect the sales of "Jennifer Garner". And when I drill down to the raw data (with it creating a new sheet) the salesperson in the column will be "Jennifer Garner"

it happens sporatically. I first noticed it always happened with a person named with the following syntax: firstname * lastname. Althought I don't think the asterisk does anything to muck up my results. It happens often enough that I don't trust it anymore

[davegugg]

A couple things:
1) This code a very difficutl to read for a couple of reasons. You should always declare all your variables at the beginning of the procedure, not throughout the code as you need them. Also, don't name a variable as the variable type (Ex: Dim Worksheet as Worksheet = bad).

2) I don't believe using the error catcher is the best way to find out if a valid selection was made. Maybe you could skip that altogether by using a listbox to capture all the available salespersons?

Other than that, I can't find anything that would cause the problem you stated above. Anyone else want to take a crack at it?

[kuraitori]

Thank you,

the error catcher is because some of the pivottables reference a query that would have all the salespeople in it (distribution of quota across provinces) and other tables reference sales (the error checker is for those items dependent on sales)