VBA / macro to change filter in pivot table

[p4nny]

hi,

I would like to change a filter within a pivot table using a vba / macro.

The filter is called "FPC", and I would like to select "Flat fee" only using the macro / vba.

When opened, by default, the pivot will show an option called "activity". I would like deselect this and select "Flat fee" instead..

Hope this is clear.

Thanks

Anthony

 

[Domenic]

Try...

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> FilterForPivotItem()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> oPivotItem <SPAN style="color:#00007F">As</SPAN> PivotItem<br>    <SPAN style="color:#00007F">Dim</SPAN> sPivotItemName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    sPivotItemName = "Flat fee"<br>    <br>    <SPAN style="color:#00007F">With</SPAN> Worksheets("Sheet1").PivotTables("PivotTable1") <SPAN style="color:#007F00">'change the sheet name and pivottable name accordingly</SPAN><br>        .ManualUpdate = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> .PivotFields("FPC")<br>            .ClearAllFilters<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> oPivotItem = .PivotItems(sPivotItemName)<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> oPivotItem <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> .Orientation = xlPageField And <SPAN style="color:#00007F">Not</SPAN> .EnableMultiplePageItems <SPAN style="color:#00007F">Then</SPAN><br>                    .CurrentPage = sPivotItemName<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oPivotItem <SPAN style="color:#00007F">In</SPAN> .PivotItems<br>                        <SPAN style="color:#00007F">If</SPAN> oPivotItem.Name <> sPivotItemName <SPAN style="color:#00007F">Then</SPAN><br>                            oPivotItem.Visible = <SPAN style="color:#00007F">False</SPAN><br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    <SPAN style="color:#00007F">Next</SPAN> oPivotItem<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .ManualUpdate = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope this helps!

 

[p4nny]

Great thanks for this.. it works however leaves an error

'Unable to set the visible property of the pivotitem class'. When I debug, it highlights this
'oPivotItem.Visible = False'

Thanks again

 

[Domenic]

Did you make any changes to the code? If so, can you post the exact code that you're using?

 

[Kurt]

Try...

<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> FilterForPivotItem()<br><br> <SPAN style="color:#00007F">Dim</SPAN> oPivotItem <SPAN style="color:#00007F">As</SPAN> PivotItem<br> <SPAN style="color:#00007F">Dim</SPAN> sPivotItemName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <br> sPivotItemName = "Flat fee"<br> <br> <SPAN style="color:#00007F">With</SPAN> Worksheets("Sheet1").PivotTables("PivotTable1") <SPAN style="color:#007F00">'change the sheet name and pivottable name accordingly</SPAN><br> .ManualUpdate = <SPAN style="color:#00007F">True</SPAN><br> <SPAN style="color:#00007F">With</SPAN> .PivotFields("FPC")<br> .ClearAllFilters<br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> oPivotItem = .PivotItems(sPivotItemName)<br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> oPivotItem <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">If</SPAN> .Orientation = xlPageField And <SPAN style="color:#00007F">Not</SPAN> .EnableMultiplePageItems <SPAN style="color:#00007F">Then</SPAN><br> .CurrentPage = sPivotItemName<br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> oPivotItem <SPAN style="color:#00007F">In</SPAN> .PivotItems<br> <SPAN style="color:#00007F">If</SPAN> oPivotItem.Name <> sPivotItemName <SPAN style="color:#00007F">Then</SPAN><br> oPivotItem.Visible = <SPAN style="color:#00007F">False</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> oPivotItem<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> .ManualUpdate = <SPAN style="color:#00007F">False</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope this helps!

I found this code from Domenic while doing a search and it does indeed work!

Great job Domenic.

Can anyone help me to modify this code so that it changes the values on 4 different pivot tables instead of clearing the filter?

This very, very close to what I need

The Worksheet names are TakeOff,CopySource, Insulattion, LF by Floor, MH by Floor, Copy Ready.

Yes I understand the worksheet names for LF by Floor need to be LFbyFloor as one name, and the same for MHbyFloor.

I want to leave this as a Sub routine so it can be called from a Menu button on a Ribbon I created.

 

[shastamccloud]

 Sub FilterPivotTable()
     Application.ScreenUpdating = False
     Sheets("table").Activate
     ActiveSheet.PivotTables("PivotTable20").ManualUpdate = True


      ActiveSheet.PivotTables("PivotTable20").PivotFields(19).ClearAllFilters


      ActiveSheet.PivotTables("PivotTable20").PivotFields(19).PivotFilters. _
    Add Type:=xlCaptionEquals, Value1:="xxx0005767"


  ActiveSheet.PivotTables("PivotTable20").ManualUpdate = False
  'Application.ScreenUpdating = True
  End Sub

Amazing how hard it is to do stuff with VBA. The code above worked for me in setting a filter on an OLAP pivot table. Note how I reference the pivot table ("PivotTable20")....I copied that from Pivot table settings in the menu bar. Next, alot of people run into problems trying to find the name or reference id of the pivot table fields. I used this: PivotFields(19)....note that I used a number, and skipped all the Excel nonsense. To find this number, I started with 1, and worked up until I found the field I was looking for.

Use the following code to help find the pivot table field number....just keep increasing the number until it spits out the data in the "Immediate" Window.

Sub piv()
Dim PF As PivotField


Set PF = ThisWorkbook.Worksheets("table").PivotTables("PivotTable20").PivotFields(19)
For Each Item In PF.VisibleItems
    Debug.Print Item.Name
   
Next Item
End Sub