View Single Post
  #1 (permalink)  
Old October 1st, 2015, 02:51 PM
tnshah tnshah is offline
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Multiple Targets in one Worksheet

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/]
Reply With Quote