Pivot Tables: error in 'Field.CurrentPage = NewCat'

[Ray707]

Thanks for your assistance guys. For some god damn reason it still isn't working for me

I've taken a screenshot of what I see on my file to hopefully help: https://ibb.co/Kyk3GK0

If you look at cell M11 you'll see 'Bacardi - Kaitlin'; this is the same value as in cell B1, and cell B1 updates via a VLOOKUP to sheet 'T&E Summary' when a number is entered in cell A1. In other words, when cell A1 is changed to '2', it brings over the client into cell B1 from 'T&E Summary', and I'm hoping to update the pivot table filter (M11) with that clients actuals.

I had a couple of attempts using the code Paul provided just above but amended it to suit my file. The first attempt was with the code below (bold is where I edited to suit my file):

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
 
    'This line stops the worksheet updating on every change, it only updates when cell
    'A1:B2 is touched
    If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub
 
    'Set the Variables to be used
    'Here you amend to suit your data
    Set pt = Worksheets("Client").PivotTables(7)
    Set Field = pt.PivotFields("TEBUD")
    NewCat = Worksheets("Client").Range("B1").Value
 
    'This updates and refreshes the PIVOT table
    Application.EnableEvents = False
    With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
    End With
    Application.EnableEvents = True
End Sub

In the code, I amended .PivotTables to 7 instead of 1, as that is the name of the pivot table in my file:

file2.jpg

I also amended .Range to B1 as this where the pivot filter should be referencing in order to update the pivot.

Unfortunately the code did not work.

I then tried another variation. In this one I changed .PivotTables to .PivotTables("PivotTable7") to see if that would work:

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim pt As PivotTable
    Dim Field As PivotField
    Dim NewCat As String
 
    'This line stops the worksheet updating on every change, it only updates when cell
    'A1:B2 is touched
    If Intersect(Target, Range("A1:B2")) Is Nothing Then Exit Sub
 
    'Set the Variables to be used
    'Here you amend to suit your data
    Set pt = Worksheets("Client").PivotTables("PivotTable7")
    Set Field = pt.PivotFields("TEBUD")
    NewCat = Worksheets("Client").Range("B1").Value
 
    'This updates and refreshes the PIVOT table
    Application.EnableEvents = False
    With pt
        Field.ClearAllFilters
        Field.CurrentPage = NewCat
        pt.RefreshTable
    End With
    Application.EnableEvents = True
End Sub

Unfortunately this did not work either. Any help would be appreciated and thanks once again guys