Should this code work in Excel 2007 as it does in 2003?

[Tim Miller]

This code below came from Debra Dalgleish:
http://www.contextures.com/excelfiles.html (Thank you Debra!). I'm not a
coder, though excited about how easy this is to follow, so I want to get
started on educating myself.

However, applying it to my own Excel 2007 workbook is having inconsistent
results. I saved my workbook as a Macro Enabled (.xlsm) file. I have 4
worksheets with a total of 6 Pivot Tables. They all share a field, located
in the Report Filter section, called "Project_Status". There are two
datasources feeding the workbook (only one of the pivot tables uses a
different source), but all 6 of the "Project_Status" filters have exactly
the same data choices.

What I'm finding is that sometimes some of the fields are updated while
others are not. Never a consistent pattern. I don't think I've once seen
ALL 6 of the filters change. Sometimes they change, but not to what I asked
for. They'll change to have multiple choices selected, not always including
the one I picked in cell D2. I thought that maybe it didn't like any of
these filters set to "Select Multiple Items". I removed that check from
each of the six filter fields. I find that when I do, and then update cell
D2 to test, some of the fields change back to "Select Multiple Items", but
do not set the value correctly (I only want one value, not multiple).
Strangely (or at least it seems so), one of my 4 worksheets does seem to
update every single time. It's the one that has 3 pivot tables on it. I
thought maybe each pivot table had to have a unique name (3 of the 6 were
still called "PivotTable1"), but that didn't do it.

Any thoughts or direction?
Thanks!
Tim


Option Explicit


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 = "Region"

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

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

For Each ws In ThisWorkbook.Worksheets
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
Next ws

End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

 

[Tim Miller]

A little more...
It appears that I had to manually choose each of the options for each of the
pivot tables. In other words, this "Project_Status" field has 5 possible
values. For the ones that weren't updating, I manually selected each of the
five directly on the pivot table... and from that point on the update to
cell D2 correctly updated that table every time. And then to another one I
did the same thing, and had the same result. It "remembered" after a save,
so now this works.
Does that make sense??

I'm hoping still to understand. This is fine for this one with only 5
possible values, but I was hoping to do this on fields where the value could
be any of thousands of numbers (order number, invoice number, etc)... so
there would be no way to prep the file for this.