unable to set the current.page priority of the pivot field class

[alfani]

Hello Excel/VBA Gurus,

Here is what caused me a headache in the past few days

I have a pivot table with a lot of data in it and I have very hard time to review it for unexpected deviations. The pivot table sales of tomatos by client by date for the last three months.
I am trying to create a macro that will help me go through the sales for each Partner (by filtering per partner in the pivot table) and look at the pivot chart that is fead by the results of the pivot table. On every "ENTER" click I want to move to the next partner and see the Chart for the sales per day for the 3 months.

I started with a code that I found in internet (see below) but when I run it I get the error in the title of the post and I do not know why.

Your help would be very much appreciated!

the code:

Sub Apply_Filter()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterClient As String

Set pvtTable = Worksheets("Sheet1").PivotTables("PivotTable2")
Set pvtField = pvtTable.PivotFields("Client Name")

filterClient = Worksheets("Sheet1").Range("c1").Value
' In "C1" I have one of the Clients just to see whether it will be picked up
' by the Pivot. Then I planned to make some loop through a list of partners ' for which I want to see the sales

For Each pvtItem In pvtField.PivotItems
If pvtItem.Value = filterClient Then
pvtField.CurrentPage = filterClient 'this is where I get the error
Exit For
End If
Next pvtItem

End Sub

 

[Andrew Poulsom]

Your code worked for me. Are you sure that Client name is a Page field?

 

[alfani]

Your code worked for me. Are you sure that Client name is a Page field?

Sorry for replying with such delay, I was on a training, obviously not on Excel/VBA

Thanks so much, Andrew!

Your question solved the issue, I simply did not have this field (Client Name) as field in the Report Filter area, it was placed instead in the Row Labels area

I will try to build the rest of the code myself and if someone is interested, will post the result.

Thanks!