vba - how to ensure correct filters applied in pivot

[esvetalalala]

hello! i receive a pivot-report which i need to modify to retrieve information and i pretty much can handle that in vba but there are various filters that need to be applied. my question is

how can i make vba code to check whether certain filter/field is applied (unticked) and do nothing. this would ease my job so much!

this is a piece of code i have... even that throws me error msg that 'new' not found.... dont know why

'Vlookup New is' pivot field and 'New' is my keyword that i need to filter out. with similar instances i get a report with a keyword is sometimes ticked or unticked...


piv.Activate
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vlookup New"). _
ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields("Vlookup New").CurrentPage _
= "New"


I'm a vba beginner... any helps is appreciated!

 

[Jerry Sullivan]

Hi esvetalalala,

If you will clarify a few details, I can suggest some code.

1. What area of the PivotTable is the field "Vlookup New" placed (Report Filters, Row Label or Column Labels)?

2. Are you trying to make the PivotItem "New" visible or hidden?

3. If "New" is to be visible do you want all the other PivotItems hidden, or should they remain unchanged?

 

[esvetalalala]

hi, thank you.
1 the area of pivot is range "A9:N155", the items are on Rows :A9:N9. then pivot is on a new sheet. in other cases when i get a ready pivot with filters located in range "A1:B5" but want to make sure some items are ticked/unticked. I dont know how to make a command like 'if in this filter this thingy is ticked then do nothing, untick otherwise'...

2 New is visible and is filtered (to show all 'new' items in the pivot)

3 the rest can stay unchanged.

many thanks for helping.

 

[Jerry Sullivan]

You could try this....

Public Sub Make_PivotItem_Visible()
    Dim PT As PivotTable
    Dim sPageSave As String
    Const sItem = "New" [COLOR="Teal"]'item to make Visible[/COLOR]
    
    Set PT = ActiveSheet.PivotTables("PivotTable2")
    
    On Error Resume Next
    With PT.PivotFields("Vlookup New")
        If .Orientation = xlPageField And _
            Not .EnableMultiplePageItems Then
                .EnableMultiplePageItems = True
                .PivotItems(sItem).Visible = True
        Else
            .PivotItems(sItem).Visible = True
        End If
    End With
End Sub

I'm not sure why your previous code had the error "new" not found.
If this code throws a similar error, we can do some debugging to spot the problem.

 

[esvetalalala]

Thank you so much!!! it does work and i can now play with visible.true and false and can get to tweak rest of the items in the pivots for my reports!! this is GREAT!!!!

 

[Jerry Sullivan]

You're welcome. Glad that this helped.