PivotTable: Adding Page Filters

**Phixer** · 08-11-2014, 02:16 PM

First, the code:

    
    ActiveSheet.PivotTables("MILEAGEDETAIL").CalculatedFields.Add "TOTAL_ANNUAL_MILEAGE", "='JULY'+'AUGUST' + 'SEPTEMBER' + 'OCTOBER' + 'NOVEMBER' + 'DECEMBER' + 'JANUARY' + 'FEBRUARY' + 'MARCH' + 'APRIL' + 'MAY' + 'JUNE'", True
    ActiveSheet.PivotTables("MILEAGEDETAIL").DisplayErrorString = True
    
    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("BE & BE TITLE")
       .Orientation = xlRowField
       .Position = 1
    End With
    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SPGM & SPGM TITLE")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCA & OCA TITLE")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("VENDOR NAME")
        .Caption = "EMPLOYEE NAME"
        .Orientation = xlRowField
        .Position = 4
    End With
    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("VENDOR ID NUMBER")
        .Orientation = xlPageField
        .Position = 1
        .CurrentPage = "EMPLOYEE"
    End With
    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OBJECT CODE")
        .Orientation = xlPageField
        .Position = 2
        .CurrentPage = "261302"
    End With

    Select Case CurrMonth
        Case "07"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
        Case "08"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
        Case "09"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
        Case "10"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
        Case "11"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
        Case "12"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
        Case "01"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JANUARY"), " JANUARY", xlSum
        Case "02"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JANUARY"), " JANUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("FEBRUARY"), " FEBRUARY", xlSum
        Case "03"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JANUARY"), " JANUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("FEBRUARY"), " FEBRUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("MARCH"), " MARCH", xlSum
        Case "04"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JANUARY"), " JANUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("FEBRUARY"), " FEBRUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("MARCH"), " MARCH", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("APRIL"), " APRIL", xlSum
        Case "05"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JANUARY"), " JANUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("FEBRUARY"), " FEBRUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("MARCH"), " MARCH", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("APRIL"), " APRIL", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("MAY"), " MAY", xlSum
        Case "06"
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JULY"), " JULY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("AUGUST"), " AUGUST", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("SEPTEMBER"), " SEPTEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OCTOBER"), " OCTOBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("NOVEMBER"), " NOVEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("DECEMBER"), " DECEMBER", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JANUARY"), " JANUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("FEBRUARY"), " FEBRUARY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("MARCH"), " MARCH", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("APRIL"), " APRIL", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("MAY"), " MAY", xlSum
            ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("JUNE"), " JUNE", xlSum
    End Select
        ActiveSheet.PivotTables("MILEAGEDETAIL").AddDataField ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("TOTAL_ANNUAL_MILEAGE"), "TOTAL YTD MILEAGE EXPENDITURES", xlSum

My problem is with this part here:

    With ActiveSheet.PivotTables("MILEAGEDETAIL").PivotFields("OBJECT CODE")
        .Orientation = xlPageField
        .Position = 2
        .CurrentPage = "261302"
    End With

I need it to filter on anything beginning with 2613, not just 261302. Unfortunately, I do not know all the possible inclusions or I could just add them individually, so I need a wildcard to add to this filter and I have never done that.

Thus, I ask how to write a wildcard filter that will work in this pivottable.

Thanks!