Please check where I have gone wrong Private Sub Worksheet_Change(ByVal Target As Range)

[PanzerAttack]

Hi been a while since I've posted and I seem to have forgetten so much.

I have a sheet (Selection) where I want my users to select from a pivot table (Cell K13) a users name. When they do so, then the same name is selected in all the other pivot tables in the workbook. As a snapshot, this is what I have. But nothing is happening, what have I left out?


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim OwnerSelection
Let OwnerSelection = ActiveSheet.Range("K13")

If Target.Address = "$K$13" Then

Sheets("AccountNumber").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Owner").CurrentPage = OwnerSelection

Application.EnableEvents = True
Application.ScreenUpdating = True

End If

End Sub

Please help, I'm at a loss.

 

[Jonmo1]

Something tells me that changing the selections in a pivot table does not qualify as a "Change" of sheet values.

No, that's not quite right.
It does qualify as a "Change", but as par pointed out, the TARGET is actually the entire pivot table's range, not just a single cell.

So you can either use the pivot table update event as I suggested, or change
If Target.Address = "$K$13" Then
to
If Not Intersect(Target, Range("K13")) Is Nothing Then

 

[Jonmo1]

Something tells me that changing the selections in a pivot table does not qualify as a "Change" of sheet values.

No, that's not quite right. It is triggering the Change Event code.
But as par pointed out, the Target is actually the entire pivot table's range, not just a single cell K13.

You can try using the pivot table events as I mentioned, or
change this
If Target.Address = "$K$13" Then
to
If Not Intersect(Target, Range("K13")) Is Nothing Then

 

[par60056]

Changing the pivot field does fire the Change event. I just wrote a little test and the target is a range of cells within the pivot table that were changed.

Target.address = "$G$1:$J$6"

So it will NEVER be just the cell that you change the filter on.

I'm certain there is a better way to do this but you could check that K13 is in the target range like this:

If (Target.Row <= 13 And Target.Row + Target.Rows.Count - 1 >= 13 And Target.Column <= 11 And Target.Column + Target.Columns.Count - 1 >= 11) Then


This will check that K is a column in the range and 13 is a row in the range but it will fire when any change is made that effects the whole table.

Or rather than checking the Target.address you could check Activecell.Address. I just tested that and it is the pivot table page field I changed.

 

[par60056]

Changing the pivot field does fire the Change event. I just wrote a little test and the target is a range of cells within the pivot table that were changed.

Target.address = "$G$1:$J$6"

So it will NEVER be just the cell that you change the filter on.

I'm certain there is a better way to do this but you could check that K13 is in the target range like this:

If (Target.Row <= 13 And Target.Row + Target.Rows.Count - 1 >= 13 And Target.Column <= 11 And Target.Column + Target.Columns.Count - 1 >= 11) Then


This will check that K is a column in the range and 13 is a row in the range but it will fire when any change is made that effects the whole table.

Or rather than checking the Target.address you could check Activecell.Address. I just tested that and it is the pivot table page field I changed.

 

[PanzerAttack]

Hi Par60056 & Jonmo1, thanks both for helping. I have incorporated what both of you have said and tied the code and used the Pivot Change. I'm still a little stuck, sorry I'm using Google to guide me as much as possible.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim OwnerSelection As String
Dim wks As Worksheet
Dim ptUpdate As PivotTable

Application.ScreenUpdating = False
Application.EnableEvents = False

OwnerSelection = pt.PivotFields("Owner").CurrentPage

For Each wks In ThisWorkbook.Worksheets

'On Error Resume Next

Set ptUpdate = wks.PivotTables(1)

If Err.Number = 0 Then
On Error GoTo 0
wks.PivotTables(1).PivotFields("Area").ClearAllFilters
wks.PivotTables(1).PivotFields("Area").CurrentPage = OwnerSelection

Sheets("Selection").Select

End If
Err.Clear

Next wks

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

I get a runtime error 424 at "OwnerSelection = pt.PivotFields("Owner").CurrentPage"

I'm guessing my way through the best I can.

 

[par60056]

CurrentPage returns a pivotItem. you need to get the .Name from it.

I made a couple other small changes.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim OwnerSelection As String
Dim wks As Worksheet
Dim ptUpdate As PivotTable

Application.ScreenUpdating = False
Application.EnableEvents = False

OwnerSelection = pt.PivotFields("Owner").CurrentPage.Name

For Each wks In ThisWorkbook.Worksheets

On Error Resume Next

   Set ptUpdate = wks.PivotTables(1)
On Error GoTo 0

   If Err.Number = 0 Then
       wks.PivotTables(1).PivotFields("Area").ClearAllFilters 
       wks.PivotTables(1).PivotFields("Area").CurrentPage = OwnerSelection
   End If
   Err.Clear

Next wks

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Rather than using the error handler to check if there is a pivot table on the wks, you might be able to check wks.PivotTables.Count

Also, you probably want a check in the loop to make sure that wks.Name is NOT the sheet they changed or you could end up in a big loop.

 

[PanzerAttack]

Thanks guys, I shall try l Target As PivotTable in due course as it's tidier, so thanks par. Jon, thanks too, I've used your Intersect and it works perfectly. I shall take sometime to read up on what it's doing too.

thanks again for your time.