CODEWITHSUNDEEP
excelvba
user12224011
user12224011

Reputation: 23

Update Pivot Table Row Label with a Cell Value

I want to filter/update Pivot Table Row Label with a cell "A1" value automatically.
I tried to stitch random codes I found:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim pt As PivotTable
Dim Field As PivotField
Dim A1Value As String

If Not Intersect(Target, Range("A1")) Is Nothing Then

Set pt = Worksheets("Sheet2").PivotTables("PivotTable1")
Set Field = pt.PivotFields("Test")
A1Value = Worksheets("Sheet1").Range("A1").Value

pt.ManualUpdate = True 

With Field
    .ClearAllFilters
    .PivotFilters.Add Type:=xlValueEquals, Value1:="A1Value"
End With

pt.RefreshTable
pt.ManualUpdate = False

End If
End Sub

I get an error

"Invalid procedure"

at .PivotFilters.Add....

I tried adding quotes on "A1Value" but I get the same error.

I also tried .PivotItems("A1Value").Visible.

I'm trying to filter the "Test" Field with a value in Cell "A1".

I have the code in Sheet 1, and the Pivot table is in Sheet 2.

Upvotes: 1

Views: 514

Answers (1)

user12224011
user12224011

Reputation: 23

I changed this line of code

.PivotFilters.Add Type:=xlValueEquals, Value1:="A1Value"

To

.PivotFilters.Add Type:=xlCaptionEquals, Value1:=A1Value

Also don't need a quotation around A1Value

Upvotes: 1

Related Questions