Unable to set the currentpage property of the pivotfield class

**adoepker** · 06-09-2011, 09:56 AM

I've created a combo box to control the same filter on 4 different pivot tables on one worksheet.
I've assigned a Macro to the combo box. The code in the Macro is as follows:


Sub Sales_Rep_Combo_Filter()
'
' Sales_Rep_Combo_Filter Macro
'

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales Rep").CurrentPage = Range("AA1").Text
    
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Rep").CurrentPage = Range("AA1").Text
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Sales Rep").CurrentPage = Range("AA1").Text
    
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Sales Rep").CurrentPage = Range("AA1").Text
    
   End Sub

When I select a field from the combo box, I get the error in the title of the thread (Unable to set the currentpage property of the pivotfield class).

Note: The cell reference for the combo box is AA1.

Any help on this error would be greatly appreciated.

**watersev** · 06-09-2011, 10:29 AM

hi, adoepker, can you post sample workbook supporting your problem?

**adoepker** · 06-09-2011, 10:45 AM

Here's an example file that illustrates the problem I'm running into. When I change the Sales Rep name in the Combo box, I want the Sales Rep Name to change in the Sales Rep filter for each of the 4 Pivot Tables.

**watersev** · 06-09-2011, 05:29 PM

hi, for pivottable2 as an example, I would suggest to do it this way:

Sub pt_2()
On Error Resume Next: Application.ScreenUpdating = False
For Each Pi In ActiveSheet.PivotTables("PivotTable2").PivotFields("Sales Rep").PivotItems
    If Pi.Name <> [aa1] Then Pi.Visible = False Else Pi.Visible = True
Next: Application.ScreenUpdating = True: End Sub

**romperstomper** · 06-09-2011, 05:52 PM

Try this:


Sub Sales_Rep_Combo_Filter()
'
' Sales_Rep_Combo_Filter Macro
'
    Dim n As Long
    Dim pt As PivotTable
    For n = 1 To 4
        Set pt = ActiveSheet.PivotTables("PivotTable" & n)
        With pt.PivotFields("Sales Rep")
            If .Orientation = xlPageField Then
                .CurrentPage = Range("AA1").Text
            ElseIf .Orientation = xlRowField Then
                pt.ManualUpdate = True
                .ClearAllFilters
                .PivotFilters.Add xlCaptionEquals, , Range("AA1").Text
                pt.ManualUpdate = False
            End If
        End With
    Next n
End Sub

**adoepker** · 06-09-2011, 06:23 PM

Thanks Romperstomper! That works great.

Watersev, I appreciate the contribution. I tried Romperstomper's first and it worked.

Is it possible to do something similar for the rest of the filters I have in the example?

I was just approaching this one step at a time, and the dilemma I ran into with the month filter is that the end user usually is concerned with year-to-date info, so multiple months would have to be selected.

I have no idea even where to start for writing a code to control the month filters for each pivot table with one combo box/list, and allow for multiple month selections.

Again, any insight is greatly appreciated. I'm relatively new to this, and I'm learning a lot on the fly.