VBA Pivot Table Filtering in conjunction with IFERROR(VLOOKUP not working

RLCornish

New Member
Joined
Feb 11, 2014
Messages
42
Hello all,

I have a pivot table and in among the many things I'm doing with it in VBA I am applying filters for 2 different data fields. One of those filters is working fine, the other appears to be working when I look at it after running the code but is in fact not. The field I'm filtering on is a calculated field from the master day that does a serious of nested VLOOKUP's within an IFERROR. This too is working fine as it's bringing back the right data. I'm stumped why the filter would not be correctly seeing this element. I'm sure it's something simple but my mind it stumped. I'll see if I can break it down here.


First step:

* Pull the STATUS of a STYLE into the MAIN DATA wksht from an outside wkbk

Second step (where the VBA code is involved):

* Filter the pivot table with STATUS as one of the 2 criteria so I only see STYLE's with "Pending" and "Approved" STATUS's

VBA code looks like this...

ActiveSheet.PivotTables("PivotTable_Dress_Ranking_w_Turns").PivotFields("Status").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable_Dress_Ranking_w_Turns").PivotFields("Status")
.PivotItems("CXLD").Visible = False
.PivotItems("Moved ").Visible = False
.PivotItems("Approved").Visible = True
.PivotItems("Pending").Visible = True
End With

I feel like it has something to with the data I'm doing the VLOOKUP to but I've tried everything, Text to Col, manually changing format, you name it. Some of the CXLD STYLE's will disappear when the VBA code is run, but not all of them.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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
Top