Filter PivotTable

tdahlman

New Member
Joined
Apr 11, 2014
Messages
14
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:

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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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

New Member
Joined
Apr 11, 2014
Messages
14
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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/
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,304
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top