Filter PivotTable

[tdahlman]

I have a pivot table with 2 Row Label ([RAMI PN] & [U of M]), 1 Column Label [Date], and one Sum of Value [Weekly Inventory].
I had a slicer set up that would allow the user to filter by [RAMI PN], but 2 of the frequent users have Excel 2007, which doesn't allow slicers.

As an alternative, I'm trying to create a userform that allows the user to type in the filter value that he wants.

My userform has 1 ComboBox [cbFilterPN] and a button that runs the following code:

Private Sub cmdFilter2_Click()
Dim pt As PivotTable
Dim FilterValue As String
FilterValue = cbFilterPN.Value

Set pt = Sheets(2).PivotTables("PivotTable1")

Sheets(2).Activate
pt.PivotFields("RAMI PN").ClearAllFilters
ThisWorkbook.RefreshAll
pt.PivotFields("RAMI PN").CurrentPage = FilterValue

Unload Me
End Sub

When I click the button [cmdFilter2] on my userform I get the following error on the line pt.PivotFields("RAMI PN").CurrentPage = FilterValue: Run-time error '1004': Unable to set the CurrentPage property of the PivotField class

I cannot figure out how to get past this. The weird thing is I've used this same code in another spreadsheet and it works out fine.

I appreciate any help

Thanks,
Travis

 

[Jerry Sullivan]

Hi Travis, You noted that "RAMI PN" is a Row Label. The .CurrentPage property can only be used for fields in the Report Filter (PageField) area of the PivotTable.

For RowFields, you need to change the .Visible property of the PivotItems.

 

[tdahlman]

Thank you for pointing that out. I knew it would end up being something dumb on my part.
Is there a way to set all records .Visible to FALSE rather than each line item separately? When I record a macro it has a line item for each record turning it to false.

 

[Jerry Sullivan]

There isn't a single step you can do through VBA that changes the manual (checkbox) filters .Visible property to False- same goes for setting all but one item to False.

If you are using Excel 2010 or later, you can use slicers to accomplish that, as Jeff Weir shared in this thread...

Daily Dose of Excel » Blog Archive » Filtering Pivots based on external ranges.

Doug Glancy shows a different method of filtering for one item by adding a helper column to the Pivot's Data Source.
http://yoursumbuddy.com/filter-pivot-tables-using-source-data-helper-columns/