Excel VBA to filter pivot table between two values

user_19906710 0
Viewed 28

I am trying to write VBA code to automatically change the filtered range in a multiple pivot tables to a desired 4 week range at the same time instead of having to manually filter them all. The Weeks are defined by week numbers 1-52 and not as actual dates. I have been unable to get any version of code to work on an individual pivot table and have not attempted to write the VBA to affect multiple tables at once. Example of pivot table and 4 week range set up

Here is the last attempt of code that I tried, it resulted in a Run-time error '1004': Application-defined or object-defined error, with it highlighting the last line of code.

Sub Updateweekrange1()
    If Range("T2").Value = "" Then
        MsgBox ("You Must First Enter a Beginning Week#.")
        Exit Sub
    End If
    
     If Range("V2").Value = "" Then
        MsgBox ("You Must First Enter a Ending Week#.")
        Exit Sub
    End If
    
    With ActiveSheet.PivotTables("Test2").PivotFields("Week")
        .ClearAllFilters
        .PivotFilters.Add Type:=xlValueIsBetween, DataField:=ActiveSheet.PivotTables("Test2").PivotFields("Week"), Value1:=Range("T2").Value, Value2:=Range("V2").Value
        
        
    End With
    
End Sub

Any ideas on what I can do to achieve this filter would be greatly appreciated. Thank you!

pivot-table filter vba excel
1 Answers

I tested the below and it worked for me.

I solved this by recording a macro (via the initially hidden developer tab), whilst I set a between filter on the Week column and then examined the generated code.

Setting wsPivot to ActiveSheet or perhaps Sheets("Sheet1") for example can allow a bit more flexibility in our coding. I'm autistic; so I can sometimes appear to be schooling others, when I'm only trying to help.


    Option Explicit
        
    Private Sub Updateweekrange1()
    
    Dim wsPivot As Worksheet
    
    Set wsPivot = ActiveSheet
    
    If wsPivot.Range("T2").Value = "" Then
        MsgBox ("You Must First Enter a Beginning Week#.")
        Exit Sub
    End If
    
     If wsPivot.Range("V2").Value = "" Then
        MsgBox ("You Must First Enter a Ending Week#.")
        Exit Sub
    End If
        
    With wsPivot.PivotTables("Test2").PivotFields("Week")
        .ClearAllFilters
        .PivotFilters.Add2 _
            Type:=xlValueIsBetween, DataField:=wsPivot.PivotTables("Test2"). _
            PivotFields("Sum of Cost"), Value1:=wsPivot.Range("T2").Value, Value2:=wsPivot.Range("V2").Value
    End With
        
    End Sub
user_19702430
Related