step 1:
Since both parts of the If use the same WITH, move it outside so
you have

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

Assuming that all the pivot tables are using the same target columns,
you could:
dim pt as pivottable

for each pt in sh.PivotTables
with pt
If Target.Column = 3 Then
ElseIf Target.Column = 4 Then
End If
End With
next


Hope this gives you pointers to solve the problem.

Peter Richardson

On Jul 24, 10:00 am, klysell wrote:
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