(repostex2) Code to refer to 6 pivot tables on same sheet

[Guest]

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!

 

[par_60056]

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

 

[Guest]

Thanks so much Peter! I'll check it out. You've definitely given me the right
path. Cheers.

 

[Guest]

Hi Peter,

I've been unsuccessful in updating all six pivot tables.. I'll keep on
trying to incorporate your changes.

Cheers,

 

[Guest]

Kent,
Are the pivot tables all on the same sheet and arranged one above the other?

What is the following line attempting to do?
..PivotFields("ITBGrp")

Is it supposed to be a WITH statement?

Are ITBGrp and IO_Grp really page fields in the pivot table?

Peter Richardson

 

[Guest]

Hi Peter,

Everything updates fine now. My client keeps demanding more complex features
regarding pivot tables. Since he wants spaces in between columns of data,
I've had to replicate three pivot tables in order to create a complex
grouping of pivot tables which are arranged to create one table. Redundant
columns in the last two pivot tables are hidden. To make it even more
complex, on the same worksheet, are another string of three pivot tables,
creating a second grouping to make up a second table. Talk about over
complicated specs... Now I am faced with a print macro that prints these
darned things. Many lines aren't even being printed, probably due to the
over-complex nature of the project.

This formatting issue is still plaguing me. I don't know how to implement
your code that you sent to my e-mail since I don't know the make up of the
pivot tables before the creation of them by the user, and the data keeps
changing which likely will reposition elements of the pivot table. I've
resorted to find-and-replace code, and this is an imperfect solution leaving
many lines unformatted.... I've tried selecting elements of the pivot table.
Here is a sample of the selection of one of the elements:
ActiveSheet.PivotTables("Project_View").PivotSelect "ITB202123P", _
xlDataAndLabel, True
This is great, except, I can only use it if the user selects this page
filter, "ITB202123P". Any other page filter (which subsquently gets parsed
into pivot tables in a worksheet and becomes each of the six pivot tables'
pages filters) chosen by the user will fail. How can I make this statement
more general? For example:
ActiveSheet.PivotTables("Project_View").PivotSelect "pi.Value", _
xlDataAndLabel, True

Will this work? It doesn't. :-(

I'll figure it out somehow, or I'll have to tell the client that it can't be
done with my beginner-level VBA skills.

Kent.