(Tom?) Pivot tables, code to refer to all pivot tables on template
 

Hi,

Tom Ogilvie helped me earlier to use a template to propagate pivot tables
using page filters, which are entered by the user into a column in a Summary
sheet. Instead of updating one pivot table (called "Project_View) on my
PIV_Template worksheet, I would like to refer to all pivot tables on this
template sheet. I've named them "Project_View" (which already updates using
the code below), "Project_View2", "Project_View3", "Project_View4",
"Project_View5", and "Project_View6". The last five pivot tables are not
referred to in the code below, and I would them to reflect the same page
filter as is updated in "Project_View".

How would I adjust the code to update all these six pivot tables rather than
just "Project_View"?

Below is the code that Tom Ogilvie helped me with.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim pi As PivotItem
Dim sh As Worksheet
If Target.Count 1 Then Exit Sub
If Len(Trim(Target)) = 0 Then Exit Sub
If Target.Column = 3 Or Target.Column = 4 Then

Sheets("PIV_Template").Visible = True

Worksheets("PIV_Template").Copy _
After:=Worksheets(Worksheets.Count)

Sheets("PIV_Template").Visible = False

Set sh = ActiveSheet
sh.Name = Target
If Target.Column = 3 Then
With sh.PivotTables("Project_View") _
..PivotFields("ITBGrp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
..CurrentPage = pi.Value
End If
Next
End With
ElseIf Target.Column = 4 Then
With sh.PivotTables("Project_View") _
..PivotFields("IO_Grp")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
..CurrentPage = pi.Value
End If
Next
End With
End If
sh.Activate
End If
End Sub

Thanks in advance!

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