PDA

View Full Version : Sync Pivot Tables



Shred Dude
04-07-2011, 10:11 AM
I'm attempting to "Sync" two pivot tables when the user changes Filtering options in one of them. It's working well if the filter is added via the "Label Filters" or "Value Filters" choices. If however you use the Check boxes to uncheck Select All, and then tick off a few choices for rows, the PivotFilters.Count Property returns zero, and thus my approach detailed below fails. The code never enters the For Each loops, as it apparently finds the PivotFilters to be empty, even though the PivotTable is clearly "filtered".

Is this a bug, or am I missing something here? How can you identify that Filters exist when they are established via selecting random Rows?

My routine:


'begin snippet...
Private Sub syncPivots(ByVal pvt As PivotTable)
Dim pvtOtherOne As Excel.PivotTable ' = Globals.wksPivot.PivotTables("pvtSummary")
Dim otherPvtField As Excel.PivotField
Dim pvtField As Excel.PivotField
Dim pvtFilter As Excel.PivotFilter
Dim pvtItem As Excel.PivotItem

'turn off Events to prevent circular firing of Pivot Table change Events
gwkbUI.Application.EnableEvents = False
'
Application.ScreenUpdating = False

'Determine which pivot table changed, set "other table" to the other one
Select Case LCase(pvt.Name)
Case "pvtmain"
Set pvtOtherOne = wks.PivotTables("pvtSummary")
Case "pvtsummary"
Set pvtOtherOne = wks.PivotTables("pvtMain")
Case Else
Exit Sub
End Select

'Loop each pivotfield in the changed pivot table looking for row fields
For Each pvtField In pvt.PivotFields
'...if it's a Row field
If pvtField.Orientation = xlRowField Then


'find the same field in the other table
Set otherPvtField = pvtOtherOne.PivotFields(pvtField.Name)
'delete each filter for that field on the other table
For Each pvtFilter In otherPvtField.PivotFilters
pvtFilter.Delete
Next
'add the new filter(s) to the other table
For Each pvtFilter In pvtField.PivotFilters
otherPvtField.PivotFilters.Add pvtFilter.FilterType, pvtField.Name, pvtFilter.Value1, pvtFilter.Value2, pvtFilter.Order, pvtFilter.Name, pvtFilter.Description
Next


'set the show detail level to be the same for each Pivot item
If pvtField.Name = "X Name" Then
For Each pvtItem In pvtField.PivotItems
otherPvtField.PivotItems(pvtItem.Name).ShowDetail = pvtItem.ShowDetail
Next
End If

End If

Next

'refresh other table
pvtOtherOne.RefreshTable

gwkbUI.Application.EnableEvents = True

'end snippet...

Kenneth Hobs
04-07-2011, 02:07 PM
I don't work with pivot tables much. If you had an example xlsm it might be easier to test.

Have you tried not turning off the enableevents globally but do it as needed?