multiple unique worksheet change events

[dgrimm]

I am trying to create a worksheet change event for multiple worksheet changes to update pivot tables. First off I am getting an error end if without if for the first one. And I know it won't reach the second because it will exit the sub if the first one is not met.

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, Sheets("Selection").Range("B17")) Is Nothing Then Exit Sub
xStr = Worksheets("Selection").Range("B17")
Application.ScreenUpdating = False
Worksheets("Pivot 2").Activate
ActiveSheet.PivotTables("BranchJob").PivotFields("Division").ClearAllFilters
ActiveSheet.PivotTables("BranchJob").PivotFields("Division").CurrentPage = xStr
ActiveSheet.PivotTables("BranchJob").RefreshTable
End If

If Intersect(Target, Sheets("Selection").Range("B18")) Is Nothing Then Exit Sub
xStr = Worksheets("Selection").Range("B18")
Application.ScreenUpdating = False
Worksheets("Pivot 2").Activate
ActiveSheet.PivotTables("BranchJob1").PivotFields("Branch Description").ClearAllFilters
ActiveSheet.PivotTables("BranchJob1").PivotFields("Branch Description").CurrentPage = xStr
ActiveSheet.PivotTables("BranchJob1").RefreshTable
End If

Application.ScreenUpdating = True
End Sub

Once I can get these two straight I can add the others.

Thank you
Dave

 

[njimack]

You don't use End If when your If statement is a single line. Try removing both the "End If"s

 

[Fluff]

Try it like

If Not Intersect(Target, Range("B17")) Is Nothing Then
   xStr = Worksheets("Selection").Range("B17")
   Application.ScreenUpdating = False
   Worksheets("Pivot 2").Activate
   ActiveSheet.PivotTables("BranchJob").PivotFields("Division").ClearAllFilters
   ActiveSheet.PivotTables("BranchJob").PivotFields("Division").CurrentPage = xStr
   ActiveSheet.PivotTables("BranchJob").RefreshTable
End If

 

[dgrimm]

Try it like

If Not Intersect(Target, Range("B17")) Is Nothing Then
   xStr = Worksheets("Selection").Range("B17")
   Application.ScreenUpdating = False
   Worksheets("Pivot 2").Activate
   ActiveSheet.PivotTables("BranchJob").PivotFields("Division").ClearAllFilters
   ActiveSheet.PivotTables("BranchJob").PivotFields("Division").CurrentPage = xStr
   ActiveSheet.PivotTables("BranchJob").RefreshTable
End If

Many thxz Fluff that did the trick....

 

[Fluff]

You're welcome & thanks for the feedback.