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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.