PDA

View Full Version : Automatically updating pivot tables



Hurrmark
03-10-2010, 12:18 PM
I have a spreadsheet which includes multiple pivot tables. I am trying to design a process in which I make a selection on one pivot table, and the other pivot tables automatically update with the same selected value that I selected in the first pivot table. For example, say I have a field "Fruit" common on five pivot tables. I select "Oranges" on the first pivot table, and the other four automatically get updated and refreshed with "Oranges" as well.

Thanks!

mbarron
03-10-2010, 01:58 PM
This changes all Pivot tables o the same sheet. Right click on the sheet tab and choose View Code. paste the code in that module.

Change the name of the PivotFields to the correct name.


Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim piv(), pivName As String, i As Integer, j As Integer, k As Integer
pivName = PivotTables(Target.Name)

Application.EnableEvents = False
Application.ScreenUpdating = False
ReDim piv(Target.PivotFields("Work Week").PivotItems.Count)
For i = 1 To Target.PivotFields("Work Week").PivotItems.Count
piv(i) = Target.PivotFields("Work Week").PivotItems(i).Visible
Next

For j = 1 To PivotTables.Count
For k = 1 To Target.PivotFields("Work Week").PivotItems.Count
If PivotTables(j).PivotFields("Work Week").PivotItems(k).Visible = piv(k) Then
'do nothing
Else
PivotTables(j).PivotFields("Work Week").PivotItems(k).Visible = piv(k)
End If
Next
Next
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Hurrmark
03-11-2010, 09:36 AM
I tried this, but got "Run time error 1004 - Unable to get the PivotItems property of the PivotField class".

Another potential issue I think might arise is the fact that the field selections are hierachal. For example, you might see something like this:

Fruit

Oranges

California
Florida

Apples

Macintosh
Fiji
Granny Smith

Vegetables

Carrots
Peppers

Will this be a problem?

Finally, what will happen in the event that you want to pick multiple selections? It doesn't seem like this may work.

Thanks.