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