PivotTable shows wrong filter

Question

Hello,

I am currently working with PivotTables in Excel for a customer who uses Excel 2010 (there is no Option to use PowerPivot).

I have 3 different PivotTables, one of them displays wrong filters, the other 2 are working fine.

What I mean is: when i choose a filter, e.g. filter for ID 12077 I get the data for ID 14084. This happens only for some of the IDs. Most data is displayed correctly.

I update the source data of the PivotTable very often, perhaps that could be the reason. But how can I fix this?

I have a macro which is using the filter and runs in the same problem. Currently I am using the command in VBA:

        ThisWorkbook.Sheets(pivotsheet).PivotTables(pivottable).PivotFields(filtername).CurrentPage = _

        Worksheets(Sheetname).Cells(i, j).Value

When I watched the mistake in the Watch Window I found out, that only the caption of the filter data is wrong. The source Name displays the correct ID.

But I couldn't find a way to use this Information.

I hope anyone can help me with it. Thanks in advance!

Kind regards

Answer 1

I found out, what my mistake was.

I was trying to set a filter with help of a makro, but one of the filters i picked did'nt exist i.e. has no data. Thats why my PivotFilter got confused and changed captions.

Thanks to Tom again. With the help of your solution i can build the PivotTable new and make sure that this won't happen again (because then i can only pick filters which are actually there)

Kind regards

Selisa_

Answer 2

Here is some pseudo code to illustrate what I mean

Dim pf as PivotField, pvtitm as PivotItem

Dim thispivottable as PivotTable

set thisPivottable = Activesheet.PivotTables(1)

pf = thispivottable.Pivotfields("name of field")

for each pvtitm in pf.PivotItems

  if pvtitm.name = cells(i,j) then

      pf.currentpage = pvtitm.name

      exit for

  end if

Next

Again, this is pseudo code - not actual code to use. 

so I compare each pivotitem name or value to the value you seek in cells(i,j). 

You might want to use something like

if Instr(1,pvtItem.name, cells(i,j), vbTextcompare) > 0 then

or some other semi-fuzzy comparison method to identify which item.

Note that this won't fix a broken table.  I know you said you already rebuilt it.  This is designed to prevent the table from getting broken (if what I describe is actually the source of the problem).

--

Regards,

Tom Ogilvy

Answer 3

Hi Tom,

I tried your idea, but I get still the same Problem. The command “.CurrentPage” gives me already the wrong ID. See here the pic below.

I would like to get the data of ID 12077 but what I get is the data of 11482. Do you have another idea which does not use Currentpage but SourceName or SourceNameStandard which seems like includes the correct data?

Answer 4

I forgot to mention, that i already build the PivotTable new. But the same mistake appeared.

How can you Loop through the items and pic the right one, if in my case the right one has a wrong caption? I do have a certain value for the filter, so the cell(i,j) i mentioned earlier is a determined cell.

Can you give me an example how the Loop works you meant?

Thanks!

Answer 5

In my experience, when manipulating currentpage with code, you can often end up setting it to a non-existing value.  This tends to "corrupt" the pivot table for lack of a better term.  You might be able to clean this up by refreshing or you may have to delete the table and build it again.

To avoid this type of problem, I usually loop through the items in the pivotfield and pick out the one I want and use that item value to assign to the currentpage. 

--

Regards,

Tom Ogilvy