Choosing multiple field filters (two) for a pivot table field with VBA for excel 2007

[Hmerman]

Hello All,

I want to filter the Row Label (which are my weeks of the year) for two selected weeks.
The value of the weeks are from two (non-contiguous) cells on the active sheet ("Sheet4") and my pivot table is located in another sheet named "Block 18".

I manage to filter for one selected week, but I can't seem to filter with more than one selection of a week.

Application.ScreenUpdating = False
    Sheets("Block 18").PivotTables("PivotTable2").ManualUpdate = True

      Sheets("Block 18").PivotTables("PivotTable2").PivotFields("WEEK").ClearAllFilters

      Sheets("Block 18").PivotTables("PivotTable2").PivotFields("WEEK").PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:=Range("R20") [U]And[/U] Range("T20")
    
  Sheets("Block 18").PivotTables("PivotTable2").ManualUpdate = False
  
 Sheets("Sheet4").Select
 
  Application.ScreenUpdating = True

I tried using AND to select another value, but it did not work. I also tried to assign another value with

Value2:=Range("T20")

But only the first value was selected.

Can someone please assist me?

I have only been trying my hand at VBA since beginning of October.

Friendly Regards
Herman

 

[pbornemeier]

You are filtering a PT field that is not a Values item. This limits you to conditionals such as =, >, <, >=, Between, etc. it does not include specifying more than one non-contiguous value.
To do this you should add an additional column to the data for the PT (and update the PT to contain that column) which puts an X in the weeks that you want to include in your PT output.

Use a code snippet to update that column to mark the rows that you want to include in the PT output based on the value of Week in that row, then filter the PT on that column.

Is your PT source a simple range? Something like: "'Block 18'!R1C4:R30C20" or is it more complex?

 

[Hmerman]

Hello,

Thank you for the reply.

I just want to know if I understand you correctly and so I just want to say sorry in advance if I come over a bit obtuse.

... To do this you should add an additional column to the data for the PT (and update the PT to contain that column) which puts an X in the weeks that you want to include in your PT output.

Do you mean I should add a column to the pt source data such as 46X for week 46 or should I add a column to the pt?

... Use a code snippet to update that column to mark the rows that you want to include in the PT output based on the value of Week in that row, then filter the PT on that column.

Is your PT source a simple range? Something like: "'Block 18'!R1C4:R30C20" or is it more complex?

The source data for the pivot table is Data!A10:M19052(Table1): the column headings are Nr Rows, Year, Date, Week, Insect type, Region, Block Nr, Farm, Block Nr & Farm, Trap Nr, Type of trap, Reference and Insect count.

The purpose of the pt is to monitor the difference between the insects (fruit flies ect.) captured from one week to the next in each block of the vineyards: So I can't seem to find a way to add a week column to the pt output, since at the moment the pt's
columns field are the years,
the rows field are the weeks and
the values field are the amount of insects for each week as well as the sum of the difference between one week to the next (which the pt output as a column).
The reports field filters farms, the type of insects and block number.

When I add a week column, the pt output does not give the data I need.

Sorry for not stating the different fields earlier!

I have written the code so it clears all filters then make each week .visible=False and the week range values chosen .visible=True.
The code gives me an error when trying to make it .visible=False at week 52. But it takes a long time to make 52 weeks false and the range values True.

    Sheets("Block 18").PivotTables("PivotTable2").ManualUpdate = True
    
    Sheets("Block 18").PivotTables("PivotTable2").PivotFields("WEEK").ClearAllFilters

    With Sheets("Block 18").PivotTables("PivotTable2").PivotFields("WEEK")
        .PivotItems("1").Visible = False
        .PivotItems("2").Visible = False
        .PivotItems("3").Visible = False
        .PivotItems("4").Visible = False
        .PivotItems("5").Visible = False
        .PivotItems("6").Visible = False
        .PivotItems("7").Visible = False
        .PivotItems("8").Visible = False
        .PivotItems("9").Visible = False
        .PivotItems("10").Visible = False
        .PivotItems("11").Visible = False
        .PivotItems("12").Visible = False
        .PivotItems("13").Visible = False
        .PivotItems("14").Visible = False
        .PivotItems("15").Visible = False
        .PivotItems("16").Visible = False
        .PivotItems("18").Visible = False
        .PivotItems("19").Visible = False
        .PivotItems("20").Visible = False
        .PivotItems("21").Visible = False
        .PivotItems("22").Visible = False
        .PivotItems("23").Visible = False
        .PivotItems("24").Visible = False
        .PivotItems("25").Visible = False
        .PivotItems("26").Visible = False
        .PivotItems("27").Visible = False
        .PivotItems("28").Visible = False
        .PivotItems("29").Visible = False
        .PivotItems("30").Visible = False
        .PivotItems("31").Visible = False
        .PivotItems("32").Visible = False
        .PivotItems("33").Visible = False
        .PivotItems("34").Visible = False
        .PivotItems("35").Visible = False
        .PivotItems("36").Visible = False
        .PivotItems("37").Visible = False
        .PivotItems("38").Visible = False
        .PivotItems("39").Visible = False
        .PivotItems("40").Visible = False
        .PivotItems("41").Visible = False
        .PivotItems("42").Visible = False
        .PivotItems("43").Visible = False
        .PivotItems("44").Visible = False
        .PivotItems("45").Visible = False
        .PivotItems("46").Visible = False
        .PivotItems("47").Visible = False
        .PivotItems("48").Visible = False
        .PivotItems("49").Visible = False
        .PivotItems("50").Visible = False
        .PivotItems("51").Visible = False
        .PivotItems("52").Visible = False
        .PivotItems(Sheets("Sheet 4").Value = Range("R20")).Visible = True
        .PivotItems(Sheets("Sheet 4").Value = Range("T20")).Visible = True
    End With
    
Sheets("Block 18").PivotTables("PivotTable2").ManualUpdate = False

Is it possible to loop through the weeks and make False the values that does not meet the ranges chosen and make True the values that has been chosen?

Please feel free to criticize where needed.

Friendly Regards
Herman

 

[pbornemeier]

This code is OK for allowing 1 or 2 filters to be specified. If you have a chance of specifying more than 2 weeks, let me know and I will show a more efficient alternate method.

Option Explicit

Sub ApplyFilters()

    Dim pi As PivotItem
    Dim lErrorCount As Long
    Dim sSpecWeek1 As String
    Dim sSpecWeek2 As String
    
    sSpecWeek1 = Sheets("Sheet 4").Range("R20").Value
    sSpecWeek2 = Sheets("Sheet 4").Range("T20").Value
    
    Sheets("Block 18").PivotTables(1).ManualUpdate = True
    
    With Sheets("Block 18").PivotTables(1).PivotFields("WEEK")
    
        .ClearAllFilters
    
        On Error Resume Next
        .PivotItems(sSpecWeek1).Visible = True
        If Err.Number <> 0 Then
            'Specified item was not present
            lErrorCount = lErrorCount + 1
            Err.Clear
        End If
        
        .PivotItems(sSpecWeek2).Visible = True
        If Err.Number <> 0 Then
            'Specified item was not present
            lErrorCount = lErrorCount + 1
            Err.Clear
        End If
        On Error GoTo 0
        
            If lErrorCount = 2 Then
            'Neither specified week exists in the data.  Can't Continue
            MsgBox "Specified Weeks: " & sSpecWeek1 & " & " & sSpecWeek2 & " are not in the data." & vbLf & vbLf & _
                "Exiting", , "Specified Weeks Not Present"
            GoTo End_Sub
        End If
        
        For Each pi In .PivotItems
            Select Case pi.Name
            Case sSpecWeek1, sSpecWeek2
                pi.Visible = True
            Case Else
                pi.Visible = False
            End Select
        Next
    End With
    
    MsgBox "Filter Applied"
    Sheets("Block 18").Activate
    
End_Sub:
    
    Sheets("Block 18").PivotTables(1).ManualUpdate = False
    
End Sub

Loops are your friend. Your original code omitted weeks 17 and 53.
You can't hide all of the rows in a pivot table. As you saw, an error will occur.

 

[Hmerman]

Remarkable!

Your code is exquisite and works very well. The way you weave each line of code to make it clear and concise is extraordinary. I do not understand everything, but I am learning.

Thank you very much for your time and wisdom I appreciate it.

Have a great day.

Friendly Regards
Herman

 

[pbornemeier]

Thank you for the compliments. This is a great site for learning to code Excel.