VBA to change Power Pivot Filters

[Andrew_UK]

Hi all,

I have built a pivot table using power pivot; it's set to tabular view so that Customer Code and Company Name are in columns A and B. When a user clicks on the Company Code or Company Name then the Macro needs to jump to the P&L Sheet and change the customer Code to match...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim pt As PivotTable
    Dim Custfield As PivotField
    Dim Salesfield As PivotField
    
    Dim i As Integer
    Dim a As String
    
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("A:B")) Is Nothing Then
            i = ActiveCell.Row
            a = Cells(i, 1).Value
            
Sheets("P&L Sheet").Select
Sheets("P&L Sheet").Activate
            
Set pt = Sheets("P&L Sheet").PivotTables("PivotTable1")
Set Custfield = pt.PivotFields("[FullCustList].[CODE].[CODE]")
            
With pt.PivotFields(Custfield.Name)
    .ClearAllFilters
    .CurrentPage = a
End With
        
        End If
    End If
    Application.ScreenUpdating = True
    Exit Sub


End Sub

The point at which it's erroring out is "CurrentPage=a", the error message I get is

"Run-time error '1004':

Unable to set the CurrentPage property of the PivotField Class"

I've tried recording the Macro, but when I then hit play again it doesn't work either. Any ideas?

Thanks in advance!!