Pivot Table not showing the value filter options - only Top 10!

Hi All,

Baffled by this - have no idea why this is happening, here is the relevant part of my code:

wb.Sheets.Add Before:=Worksheets("Data")
ActiveSheet.Name = "PD"
Set ws = Sheets("PD")
ws.Select
wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data", Version:=xlPivotTableVersion10).CreatePivotTable TableDestination:= _
        "PD!R1C1", TableName:="PD Exceptions", DefaultVersion:=xlPivotTableVersion12

Set pt = ws.PivotTables("PD Exceptions")

With pt.PivotFields("Transaction Method")
        .Orientation = xlPageField
        .Position = 1
        .PivotItems("NSF").Visible = False
End With
With pt.PivotFields("Account Number")
    .Orientation = xlRowField
    .Position = 1
    .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
With pt.PivotFields("Product")
        .Orientation = xlRowField
        .Position = 2
        .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
With pt.PivotFields("Posting Dt")
        .Orientation = xlRowField
        .Position = 3
        .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With

pt.AddDataField pt.PivotFields("Posting Amt"), "Posting Amt.", xlSum
pt.AddDataField pt.PivotFields("Deposit Amt"), "Deposit Amt.", xlSum
pt.CalculatedFields.Add "PDEx", "='Posting Amt' -'Deposit Amt'", True

With pt.PivotFields("PDEx")
    .Orientation = xlDataField
    .Caption = "Variance"
End With

With ActiveSheet.PivotTables("PD Exceptions").DataPivotField
    .Orientation = xlColumnField
    .Position = 1
End With

pt.RefreshTable

pt.PivotFields("Account Number").PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pt.PivotFields("Variance"), Value1:=0
pt.PivotFields("Posting Dt").PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pt.PivotFields("Variance"), Value1:=0

The code breaks when it gets to the last 2 lines (in Red) because the pivot table does not show the filter options to filter by "Greater Than", in fact it only shows the option to use the Top 10 and nothing else. Here is a screenshot attached.2014-02-13 16_07_23-Microsoft Excel - Exception Reporting.xlsm [Compatibility Mode].png

Any ideas why this is?

I had created the same pivot table from the same data without VB before and that showed the other filter options. the only difference I could find is that in that the data fields are called "Values" but in this the heading says "Data" - attached is a screenshot of the manual pivot.

2014-02-13 16_26_05-Microsoft Excel - Zakhiem Citi Report 10 2013.xlsm.png

All help is greatly appreciated.
Thanks.

Originally Posted by MarvinP

Hi,

I find, when this kind of stuff happens to me, it is because there is text somewhere in my data. I've also seen problems when using VBA and trying to filter by something that isn't available. I'd look at your Pivot Table Range to insure it doesn't have a blank row or cell which keeps the data from being all numbers.

Think about this. Can you have a number filter if you have both numbers and text?

Thanks for you reply.

That is one of the things I tried before posting here. There are no text in any of the 3 data fields.

The data fields are all numbers - Posting Amt, Deposit Amt, Variance - the first 2 are numbers formatted as currency in the data, the last one is a calculated field, which is basically Dep Amt - Post Amt - so that is also a number.

Now the row-fields - Account Number, Product and Posting Date - are all text and date formats, but that should not affect anything right?