Combine 2 codes in VBA

[Dokat]

Hi,

How can i combine below 2 codes? Also for lookup values i need to combine cells C4&F4. To do is this code makes sense?

If Intersect(Target, Target.Worksheet.Range("C4" & "F4")) Is Nothing Then Exit Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String


On Error Resume Next
If Intersect(Target, Target.Worksheet.Range("B4")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Source Data").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("Geography")
xStr = Target.Value
With xPFile
    .ClearAllFilters
    .PivotFilters.Add2 xlCaptionEquals, Value1:=xStr
End With
Application.ScreenUpdating = True


On Error Resume Next
If Intersect(Target, Target.Worksheet.Range("C4" & "F4")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Source Data").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("% Dollar Sales by Merch Any Price Reduction")
xStr = Target.Value
With xPFile
    .ClearAllFilters
    .PivotFilters.Add2 xlCaptionEquals, Value1:=xStr
End With
Application.ScreenUpdating = True


End Sub

 

[NdNoviceHlp]

Seems like this would work..

If Intersect(Target, Target.Worksheet.Range("C4")) Is Nothing OR _
Intersect(Target, Target.Worksheet.Range("F4")) Is Nothing Then Exit Sub

HTH. Dave

 

[Dokat]

I used a different cell (A3) for to combine c4&f4. However when i ran second code doesnt work and filter the pivot table. What maybe causing this?

Dim xPTable As PivotTable
Dim xPFile As PivotField
Dim xStr As String


On Error Resume Next
If Intersect(Target, Target.Worksheet.Range("B4")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Source Data").PivotTables("PivotTable1")
Set xPFile = xPTable.PivotFields("Geography")


xStr = Target.Value
With xPFile
    .ClearAllFilters
    .PivotFilters.Add2 xlCaptionEquals, Value1:=xStr
End With
Application.ScreenUpdating = True




If Intersect(Target, Target.Worksheet.Range("A3")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set xPTable = Worksheets("Source Data").PivotTables("PivotTable1")
Set xPFile1 = xPTable.PivotFields("% Dollar Sales by Merch Any Price Reduction")
xStr = Target.Value
With xPFile1
    .ClearAllFilters
    .PivotFilters.Add2 xlCaptionEquals, Value1:=xStr
End With
Application.ScreenUpdating = True


End Sub

 

[NdNoviceHlp]

Not sure but U are using a worksheet change event... I hate them because if the code makes a sheet change then the code begins again before the initial code is actually completed. Not sure if that's happening here because I also hate filters and pivot tables and have no experience with their coding. Making A3 = C4 & F4 is not the same thing as doing something if either C4 or F4 is nothing. Anyways, trial just making a sub and running the code (ie. remove it from the ws change event) and see what happens. Dave