Hi,

I have a template that creates pivot tables using a macro on my 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.

How would I adjust the code below to update "Project_View2",
"Project_View3", "Project_View4", "Project_View5" and "Project_View6?

For example, the following code:
"With sh.PivotTables("Project_View")"
refers to just Pivot table named "Project_View". How do I refer (and update)
the other five pivot tables on the same sheet?

Here is the full macro code:

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