Updating pivot items on 3 pivot tables contained on one sheet
 

Hi,

I have this code that updates pivot items on all three pivot tables which
reside on one sheet. Why doesn't this code work? When the user selects a
drop-down pivot item (named the "DAC" field) in cell H2, all pivot items
should updated to reflect this new value for the DAC field. Below is the
code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws, pt, pi, strField blah, blah, blah
strField = "DAC"
On Error Resume Next
If Target.Address = Range("H2").Address Then
For Each pt In ws.PivotTables
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
Next pt
End If

End Sub


Does anyone have any answers?

Thank-you for your help!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Updating pivot items on 3 pivot tables contained on one sheet
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws, pt, pi, strField blah, blah, blah
strField = "DAC"
'On Error Resume Next
If Target.Address = Range("H2").Address Then
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
..CurrentPage = pi.Value 'Target.Value
Exit For
Else
..CurrentPage = "(All)"
End If
Next pi
End With
Next pt
End If

End Sub

I assume the code is executing but not changing the value.

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi,

I have this code that updates pivot items on all three pivot tables which
reside on one sheet. Why doesn't this code work? When the user selects a
drop-down pivot item (named the "DAC" field) in cell H2, all pivot items
should updated to reflect this new value for the DAC field. Below is the
code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws, pt, pi, strField blah, blah, blah
strField = "DAC"
On Error Resume Next
If Target.Address = Range("H2").Address Then
For Each pt In ws.PivotTables
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
Next pt
End If

End Sub


Does anyone have any answers?

Thank-you for your help!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

Updating pivot items on 3 pivot tables contained on one sheet
 

Hi Tom,

Thanks for your reply. I made your minor change in the code and got this
message: "Object variable or With block variable not set". Would you have any
idea what I'm doing wrong?

Thanks again!
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557


"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws, pt, pi, strField blah, blah, blah
strField = "DAC"
'On Error Resume Next
If Target.Address = Range("H2").Address Then
For Each pt In ws.PivotTables
With pt.PageFields(strField)
For Each pi In .PivotItems
If pi.Value = Target.Value Then
.CurrentPage = pi.Value 'Target.Value
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Next pt
End If

End Sub

I assume the code is executing but not changing the value.

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi,

I have this code that updates pivot items on all three pivot tables which
reside on one sheet. Why doesn't this code work? When the user selects a
drop-down pivot item (named the "DAC" field) in cell H2, all pivot items
should updated to reflect this new value for the DAC field. Below is the
code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws, pt, pi, strField blah, blah, blah
strField = "DAC"
On Error Resume Next
If Target.Address = Range("H2").Address Then
For Each pt In ws.PivotTables
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
Next pt
End If

End Sub


Does anyone have any answers?

Thank-you for your help!

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557