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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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.
 
Upvote 0
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/
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top