Multiple Targets in one Worksheet

tnshah · October 1st, 2015, 02:51 PM

I wrote the below code, however:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Geography"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

    If Target.Address = Range("B18").Address Then
 

        Set ws = ThisWorkbook.Worksheets("DataPivotCategory")
            Set pt = ws.PivotTables("PivotTable3")
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
End If
    

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I need to do multiple changes in this worksheet; i.e. i also need Cell B51 to be target value for a pivot item of a different pivot (per below). How do i do this if i can't have multiple target addresses in one module?

[code]
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Geography"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

If Target.Address = Range("B51").Address Then

Set ws = ThisWorkbook.Worksheets("DataPivotCategory")
Set pt = ws.PivotTables("PivotTable4")
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
[code/]

Zakalwe · October 31st, 2015, 10:06 AM

Hi,

1. You are using the same code in both PivotTable3 & PivotTable4, after this: If Target.Address = Range("xxx").Address Then
Why not wrap this code in the function/Sub then call the same function twice, supplying the pivot table as a parameter for each call?

2. This is the same thing:

Code:

If Target.Address = Range("B18").Address Then

/

Code:

If Target.Address = Range("B51").Address Then

Select case Target.Address
Case "B18"
'Relevant Code
Case "B51"
'Relevant Code

Or ifThenElse.
If Target.Address = "B18" Then
'Relevant Code
ElseIf Target.Address = "B51"
'Relevant Code
Else
'Relevant Code
EndIf

On Worksheet Change the check the cell/Target that changed/triggered the Change event and execute relevant/unique steps applicable for that Target/TargetValue.

[email protected]