Filtering more than one value on a pivot using vba

[Christof]

Hi,

Can you help me with my code please?
I want to able to type a year into cell G3, and that will then update my pivot tables with filtering Field "FY2" or "Cal Year" with the year in cell G3 and the year before that.
eg: if I type "2018" into G3, it will filter 2018 and 2017 in my pivot tables.



Below is my code.
Apologies if it's a bit crude, I'm very new to this stuff.
Your help would be greatly appreciated.

Kind Regards
Christof

Sub test()

Application.Calculation = xlManual


'Set the Variables to be used
Dim pt, pt2, pt3, pt4 As PivotTable
Dim Field, Field2, Field3, Field4, Field5, Field6, Field7, Field8, Field9, Field10, Field11, Field12 As PivotField
Dim NewCat, NewCat2, NewCat3, NewCat4 As String
Dim pi As PivotItem

'Here you amend to suit your data
Set pt = Worksheets("Product Summary").PivotTables("PivotTable1")
Set pt2 = Worksheets("Product Summary").PivotTables("PivotTable2")
Set pt3 = Worksheets("Product Summary").PivotTables("PivotTable3")
Set pt4 = Worksheets("Product Summary").PivotTables("PivotTable4")
Set Field = pt.PivotFields("Brand")
Set Field2 = pt.PivotFields("New Product Group")
Set Field3 = pt.PivotFields("Cal Year")
Set Field4 = pt2.PivotFields("Brand")
Set Field5 = pt2.PivotFields("New Product Group")
Set Field6 = pt2.PivotFields("FY2")
Set Field7 = pt3.PivotFields("Brand")
Set Field8 = pt3.PivotFields("New Product Group")
Set Field9 = pt3.PivotFields("Cal Year")
Set Field10 = pt4.PivotFields("Brand")
Set Field11 = pt4.PivotFields("New Product Group")
Set Field12 = pt4.PivotFields("FY2")

pt.AllowMultipleFilters = True
pt2.AllowMultipleFilters = True
pt3.AllowMultipleFilters = True
pt4.AllowMultipleFilters = True

NewCat = Worksheets("Product Summary").Range("e5").Value
NewCat2 = Worksheets("Product Summary").Range("e7").Value
NewCat3 = Worksheets("Product Summary").Range("g3").Value
NewCat4 = Worksheets("Product Summary").Range("g3").Value - 1


With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
Field2.ClearAllFilters
Field2.CurrentPage = NewCat2
Field3.ClearAllFilters
Field3.CurrentPage = NewCat3 And NewCat4

pt.RefreshTable

End With

With pt2
Field4.ClearAllFilters
Field4.CurrentPage = NewCat
Field5.ClearAllFilters
Field5.CurrentPage = NewCat2
Field6.ClearAllFilters
Field6.CurrentPage = NewCat3 And NewCat4

pt2.RefreshTable

End With

With pt3
Field7.ClearAllFilters
Field7.CurrentPage = NewCat
Field8.ClearAllFilters
Field8.CurrentPage = NewCat2
Field9.ClearAllFilters
Field9.CurrentPage = NewCat3 And NewCat4

pt3.RefreshTable

End With

With pt4
Field10.ClearAllFilters
Field10.CurrentPage = NewCat
Field11.ClearAllFilters
Field11.CurrentPage = NewCat2
Field12.ClearAllFilters
Field12.CurrentPage = NewCat3 And NewCat4

pt4.RefreshTable

End With

Calculate

End Sub

 

[Stevie]

Hi Christof,
without a copy of your file I cannot test, but I believe if you replace

Field3.CurrentPage = NewCat3 And NewCat4

with

field3.PivotItems(NewCat3).Visible = False
field3.PivotItems(NewCat4).Visible = False

Then it might do what you wish.
Let me know how it goes.

EDIT:

After rereading your post, it appears that you may want to filter out everything but 'NewCat3' or 'NewCat4'. If this is the case, try the below code instead:

Dim pi As PivotItem
For Each pi In field3.PivotItems
    If Not ((pi.name = NewCat3) Or (pi.name = NewCat4)) Then
        pi.Visible = False
    End If
Next

If you found this useful, click 'Like' below!

Stevie

 

[Chihiro]

If you have Excel 2010 or later. I'd strongly recommend loading your data to data model and basing your Pivot Table off of it.

That'd change Pivot type to OLAP based, and you can use PivotField.VisibleItemsList to set array of visible items in one shot.

 

[Christof]

Thanks for your help..
I have attached a link to my file if that's easier.
I tried your suggestions, but i couldnt get them working for me.. but I was probably pasting them in the wrong place or something.

https://drive.google.com/open?id=1TzaJ97Q4q2sCwkQnyDMICAdB7nwwd8xZ

Just to clarify, If i select "2018" from cell G3, then I want all 4 pivots to refresh with 2018 and 2017 selected in either the "FY2" field or "Cal Year" field.

Thanks guys

 

[Stevie]

Hi Christof.

Firstly, nothing will happen when you change the value of G3, as your code is just in a sub called test.
As explained to you in your previous post, you must use a Worksheet_Change sub to capture this event.

Secondly, your code contains a few 'with' statements which are doing nothing at all as the code between is referencing previously defined variables instead of the object specified by the with.

Thirdly, your variable declaration is very strange. You declare one of each item as a pivot table type object (e.g. PivotField) and the rest as variants. 'Field2' for example in your code is a variant and 'Field12' is a PivotField.
This wont cause a problem as such, but it is very confusing and bad syntax. If you did this to a variable which had a specific requirement, then this could cause serious issues. If you don't know what I mean by this, try this link http://learnexcelmacro.com/wp/2012/10/declaring-multiple-variables-with-one-dim-statement/

I have made these changes and a few others and simply pasted my code from above in and it seems to be working fine.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("G3")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing then
        Application.Calculation = xlManual

        'Set the Variables to be used
        Dim pt As PivotTable
        Dim pt2 As PivotTable
        Dim pt3 As PivotTable
        Dim pt4 As PivotTable
        Dim Field As PivotField
        Dim Field2 As PivotField
        Dim Field3 As PivotField
        Dim Field4 As PivotField
        Dim Field5 As PivotField
        Dim Field6 As PivotField
        Dim Field7 As PivotField
        Dim Field8 As PivotField
        Dim Field9 As PivotField
        Dim Field10 As PivotField
        Dim Field11 As PivotField
        Dim Field12 As PivotField
        Dim NewCat As String
        Dim NewCat2 As String
        Dim NewCat3 As String
        Dim NewCat4 As String
        Dim pi As PivotItem

        'Here you amend to suit your data
        Set pt = Worksheets("Product Summary").PivotTables("PivotTable1")
        Set pt2 = Worksheets("Product Summary").PivotTables("PivotTable2")
        Set pt3 = Worksheets("Product Summary").PivotTables("PivotTable3")
        Set pt4 = Worksheets("Product Summary").PivotTables("PivotTable4")
        Set Field = pt.PivotFields("Brand")
        Set Field2 = pt.PivotFields("New Product Group")
        Set Field3 = pt.PivotFields("Cal Year")
        Set Field4 = pt2.PivotFields("Brand")
        Set Field5 = pt2.PivotFields("New Product Group")
        Set Field6 = pt2.PivotFields("FY2")
        Set Field7 = pt3.PivotFields("Brand")
        Set Field8 = pt3.PivotFields("New Product Group")
        Set Field9 = pt3.PivotFields("Cal Year")
        Set Field10 = pt4.PivotFields("Brand")
        Set Field11 = pt4.PivotFields("New Product Group")
        Set Field12 = pt4.PivotFields("FY2")

        pt.AllowMultipleFilters = True
        pt2.AllowMultipleFilters = True
        pt3.AllowMultipleFilters = True
        pt4.AllowMultipleFilters = True

        NewCat = Worksheets("Product Summary").Range("e5").Value
        NewCat2 = Worksheets("Product Summary").Range("e7").Value
        NewCat3 = Worksheets("Product Summary").Range("g3").Value
        NewCat4 = Worksheets("Product Summary").Range("g3").Value - 1

        pt.ClearAllFilters
        Field.CurrentPage = NewCat
        Field2.CurrentPage = NewCat2
        For Each pi In Field3.PivotItems
            If Not ((pi.Name = NewCat3) Or (pi.Name = NewCat4)) Then
                pi.Visible = False
            End If
        Next
        pt.RefreshTable

        pt2.ClearAllFilters
        Field4.CurrentPage = NewCat
        Field5.CurrentPage = NewCat2
        For Each pi In Field6.PivotItems
            If Not ((pi.Name = NewCat3) Or (pi.Name = NewCat4)) Then
                pi.Visible = False
            End If
        Next
        pt2.RefreshTable

        pt3.ClearAllFilters
        Field7.CurrentPage = NewCat
        Field8.CurrentPage = NewCat2
        For Each pi In Field9.PivotItems
            If Not ((pi.Name = NewCat3) Or (pi.Name = NewCat4)) Then
                pi.Visible = False
            End If
        Next
        pt3.RefreshTable

        pt4.ClearAllFilters
        Field10.CurrentPage = NewCat
        Field11.CurrentPage = NewCat2
        For Each pi In Field12.PivotItems
            If Not ((pi.Name = NewCat3) Or (pi.Name = NewCat4)) Then
                pi.Visible = False
            End If
        Next
        pt4.RefreshTable

        Application.Calculation = xlAutomatic
    End If
End Sub

If any of this has been helpful, please click 'Like' on the bottom right.
Stevie