Filter Pivot Table based on Cell value, if no value then select ALL

[John_356]

Good day,

Browsing through the forum I have found this code which works for me. I would need an additional function, if J14 = "" then filter to ALL instead of Exit Sub:

[B]Private[/B] [B]Sub[/B] Worksheet_SelectionChange([B]ByVal[/B] Target [B]As[/B] Range)

If Intersect(Target, Range("J14")) Is Nothing Then Exit Sub

Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String
Set pt = Worksheets("sheet1").PivotTables("pivottable1")
Set Field = pt.PivotFields("Month")
NewCat = Worksheets("sheet2").Range("J14").Value

With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

[B]End Sub[/B]

Many thanks in advance.

John

 

[John_356]

UPDATE

I've tried using the code with below changes, yet again without success:

If Intersect(Target, Range("J17")) Is Nothing Then ActiveSheet.PivotTables("pivottable1").PivotFields("Year").CurrentPage = "(All)"
    ActiveSheet.PivotTables("pivottable1").PivotFields("Month").EnableMultiplePageItems = _
    True
Set pt = Worksheets("sheet1").PivotTables("pivottable1")
Set Field = pt.PivotFields("Year")
NewCat = Worksheets("MACRO").Range("J17").Value
With pt
Field.ClearAllFilters
Field.CurrentPage = NewCat
pt.RefreshTable
End With

FYI I'll be running the code twice as I have two filters, Month and Year, if they're not selected and their cells are blank I would like the code to filter to ALL Years and Months available. If only one is selected then filtering applies to that one, with the other one set to ALL once more.

Thanks again!

J