Changing Multiple Pivot table page filters automatically based on the first pivot table.

beerw0lf

Board Regular
Joined
Mar 7, 2006
Messages
103
Hello All,
I have searched the boards and have not been able to locate exactlly what I need. I am hoping that once again I can learn for the best. I apoligize up front if this topic has already been covered. I have 3 pivot tables in the same worksheet. Each pivot table pulls from the same pool of data and is layed out exactly the same. Each pivot table keys off of different row lables. I am utilizing 2 report filter options. What I am trying to accomplish is for 2 of the pivot tables to update to the same report filter options that I enter in the first pivot table. I have forud code that works with all pivot table in all worksheets with one report option. I just want the 3 pivot tables in this one sheet to work together. Any and all help will be greatly appreciated. THANKS!!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Thanks crimson b1ade. That's great. Unfortunately I need to select multiple items from within the list and this sample does not seem to work with that. It will work with my first filter which just deals with 1 item but my second filter deals with multiple items.

Thanks again.
 
Upvote 0
Hello All. I came across the followintg code and tweaked it slightly to accomplish my goal except for 1 thing. This code only allows me to either select "ALL" or select 1 item at a time from my second (Equipment Make) filter feild. Since I am clueless would someone be so kind as to let me know if the code can be tweaked to allow multiple selection to my second filter. Below is the code.

Thanks - you don't know how much I appreciate the help.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
''based on code by Robert Rosenberg posted 2000/01/11
''A module level variable (mvPivotPageValue) keeps
''track of the last selection from the Page Field.
Dim wsOther As Worksheet
Dim pt As PivotTable
Dim pt1 As PivotTable
Dim pt2 As PivotTable
Dim pt3 As PivotTable
Dim pi As PivotItem
Dim strField1 As String
Dim strField2 As String
Dim strField3 As String
Dim strField4 As String

strField1 = "Equipment Make"
strField2 = "Equipment Model"


Set wsOther = Sheets("Other Pivots")
Set pt = Target
Set pt1 = wsOther.PivotTables("PT1")
Set pt2 = wsOther.PivotTables("PT2")
Set pt3 = wsOther.PivotTables("PT3")

On Error Resume Next
If LCase(pt.PivotFields(strField1).CurrentPage) <> LCase(mvPivotPageValue1) Then
'The PageField1 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue1 = pt.PivotFields(strField1).CurrentPage

With pt1.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt2.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt3.PageFields(strField1)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue1 Then
.CurrentPage = mvPivotPageValue1
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Application.EnableEvents = True
End If

If LCase(pt.PivotFields(strField2).CurrentPage) <> LCase(mvPivotPageValue2) Then
'The PageField2 was changed
Application.EnableEvents = False
pt.RefreshTable
mvPivotPageValue2 = pt.PivotFields(strField2).CurrentPage
With pt1.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt2.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
With pt3.PageFields(strField2)
For Each pi In .PivotItems
If pi.Value = mvPivotPageValue2 Then
.CurrentPage = mvPivotPageValue2
Exit For
Else
.CurrentPage = "(All)"
End If
Next pi
End With
Application.EnableEvents = True
End If

End Sub
 
Upvote 0
I also found this code that sounds like it does what I want but I gett an debug error when I try to run it on the following line: "Debug.Print PF.CurrentPageName". any help will be appreciated as I know nothing about VB. THANKS!!

Public Sub SyncPivots()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim PT1 As PivotTable
Dim PT2 As PivotTable
Dim PF As PivotField
Set ws1 = ThisWorkbook.Worksheets("Pivots")
Set ws2 = ThisWorkbook.Worksheets("Other Pivots")
Set PT1 = ws1.PivotTables("PT1")
Set PT2 = ws2.PivotTables("PT2")
For Each PF In PT1.PivotFields
If PF.Orientation = xlPageField Then
Debug.Print PF.CurrentPageName
'On Error Resume Next ' comment in at the end (in case PTs are not the same)
PT2.PivotFields(PF.Name).CurrentPageName = PF.CurrentPageName
End If
Next PF

End Sub
 
Upvote 0
Hello All,
I was able to switch gears and grind my way to a solution to my issue. I took a totally different path but was able to acccomplish what I was trying to do.

Thanks
 
Upvote 0
Good video, but a couple of suggestions for improvement. I would create another pivot table to just display the city list and create a dynamic named range to refer to the city list in the pivot table. This will ensure you always have an updated, valid list and you don't have to manually update your list.

this link should help. it shows you how to link pivot tables using a combo box: http://www.datapigtechnologies.com/flashfiles/pivot10.html
 
Upvote 0
Please help! The recorded code for this causes a runtime error 5. Invalid Procedure Call or Arguement when selecting a different option than what it was recorded with. I think this is new b/c i've worked with this before and it never caused a problem in the past.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top