Set Multiple Pivot Filters By Cell Value

[RJW2809]

Hi,

I'm wanting to set a pivot table to filter the results according to values set by which ever cell is selected in the main worksheet. There are three cell ranges which if selected will give differing results. Within the pivot table itself there are four fields which could be filtered but only one or two of them would be set with a value at any one time, the rest need to be (All). As other settings are changed this would then need to be reflected in the pivot settings.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
'Part 1 (works fine) this sets two values based on the cell that is selected in the range and sets values that to be referenced in part 3
    If Not Intersect(Target, Range("AA16:AQ32")) Is Nothing Then
'Filter Values to be used
        Worksheets("FC_LocationExceptions").Range("O14").Value = Selection.Offset(0, 31).Value
        Worksheets("FC_LocationExceptions").Range("O15").Value = Selection.Offset(0, 51).Value
'Filter Fields to be used
        Worksheets("FC_LocationExceptions").Range("P14").Value = Range("U12")
        Worksheets("FC_LocationExceptions").Range("P15").Value = Range("U13")


'Part 2 (presume works as no error here) clears the pivot table filters
With Worksheets("FC_LocationExceptions").PivotTables("LocationExceptions")
.PivotFields("DailyBiasGroup").ClearAllFilters
.PivotFields("ReturnQtyGroup").ClearAllFilters
.PivotFields("ReturnValueGroup").ClearAllFilters
.PivotFields("ZeroReturnGroup").ClearAllFilters


'Part 3 (where I'm gettng an error) sets the 4 filter values to the results of the calculations in part 1.
'Any two of the fields will be set to (All) and the other two will have numeric values resulting from part 1.
'Which two are set to (All) and which have values will be variable depending on other selections in the sheet
.PivotFields("DailyBiasGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V3].Value
.PivotFields("ReturnQtyGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V4].Value
.PivotFields("ReturnValueGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V5].Value
.PivotFields("ZeroReturnGroup").PivotFilters.Add Type:=xlCaptionEqualss, Value1:=[V6].Value
'The process potentially then repeats if a different cell range is selected but with the same intended results, however in the below two there would be three fields with a value of (All)
End With


    End If
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("AA14:AQ14")) Is Nothing Then
        Worksheets("FC_LocationExceptions").Range("O14").Value = Selection.Offset(0, 31).Value
        Worksheets("FC_LocationExceptions").Range("O15").Value = Selection.Offset(0, 31).Value
        Worksheets("FC_LocationExceptions").Range("P14").Value = Range("U12")
        Worksheets("FC_LocationExceptions").Range("P15").Value = Range("U12")


With Worksheets("FC_LocationExceptions").PivotTables("LocationExceptions")
.PivotFields("DailyBiasGroup").ClearAllFilters
.PivotFields("ReturnQtyGroup").ClearAllFilters
.PivotFields("ReturnValueGroup").ClearAllFilters
.PivotFields("ZeroReturnGroup").ClearAllFilters


.PivotFields("DailyBiasGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V3].Value
.PivotFields("ReturnQtyGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V4].Value
.PivotFields("ReturnValueGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V5].Value
.PivotFields("ZeroReturnGroup").PivotFilters.Add Type:=xlCaptionEqualss, Value1:=[V6].Value
End With


    End If
If Selection.Count = 1 Then
    If Not Intersect(Target, Range("Y16:Y32")) Is Nothing Then
        Worksheets("FC_LocationExceptions").Range("O14").Value = Selection.Offset(0, 51).Value
        Worksheets("FC_LocationExceptions").Range("O15").Value = Selection.Offset(0, 51).Value
        Worksheets("FC_LocationExceptions").Range("P14").Value = Range("U13")
        Worksheets("FC_LocationExceptions").Range("P15").Value = Range("U13")


With Worksheets("FC_LocationExceptions").PivotTables("LocationExceptions")
.PivotFields("DailyBiasGroup").ClearAllFilters
.PivotFields("ReturnQtyGroup").ClearAllFilters
.PivotFields("ReturnValueGroup").ClearAllFilters
.PivotFields("ZeroReturnGroup").ClearAllFilters


.PivotFields("DailyBiasGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V3].Value
.PivotFields("ReturnQtyGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V4].Value
.PivotFields("ReturnValueGroup").PivotFilters.Add Type:=xlCaptionEquals, Value1:=[V5].Value
.PivotFields("ZeroReturnGroup").PivotFilters.Add Type:=xlCaptionEqualss, Value1:=[V6].Value
End With


    End If
End If
End If
End If
End Sub

Any help would be much appreciated.
Thanks in advance,
RJW