Update row and colum filter of a pivot table with VBA

[davsebsav]

Hi there,

I have some difficulties updating the row and column filter of a Pivot table by using the value of two cells on a different spreadsheet. The main Report filter automatic update works fine but when I Change the Categories or Company the formula stop at this line:

pfcat.CurrentPage = categ

Does anyone have any idea about what could be the cause?

Thanks in advance for your help


Private Sub Worksheet_Change(ByVal Target As Range)
'The sub automatically update the PT filter according to the region in the Userform
Dim pvt As PivotTable
Dim pf As PivotField
Dim pfcat As PivotField
Dim pfcomp As PivotField
Dim region As String
Dim categ As String
Dim comp As String

Set pvt = Worksheets("Sheet2").PivotTables("PivotTable1")
Set pf = pvt.PivotFields("Geographies")
Set pfcat = pvt.PivotFields("Code Category")
Set pfcomp = pvt.PivotFields("Companies")
region = Worksheets("Userform").Range("D4").Value
categ = Worksheets("Userform").Range("F4").Value
comp = Worksheets("Userform").Range("B4").Value
With pvt
pf.ClearAllFilters
pf.CurrentPage = region
pfcat.ClearAllFilters
pfcat.CurrentPage = categ
pfcomp.ClearAllFilters
pfcomp.CurrentPage = comp
pvt.RefreshTable
End With
End Sub