Three Pivot Tables - One Filter?

[chowyg]

I'm using Excel 2007 and I had a specific question. I have some raw data that is from A1:H500 and I made three pivot tables out of the same data. Those three pivot tables then feed into a model that I created. The only problem I have is that in my three pivot tables each of them have a "report filter" that is independent of all the other pivot tables. Can I create one "report filter' that will automatically filter all three pivot tables or will I have to filter each pivot table one at a time?

 

[Greg Truby]

Welcome to the board. The way I've done that in the past is to use the worksheet's PivotTableUpdate event handler to sych them up. In this sample the pivots are on the same worksheet and the page field (filter) is "Product".

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
    Dim s$    
    
    Application.EnableEvents = False
    
    If Target.Name = "PivotTable1" Then
        With Me.PivotTables
            Let s = .Item("PivotTable1").PivotFields("Product").CurrentPage
            .Item("PivotTable2").PivotFields("Product").ClearAllFilters
            .Item("PivotTable2").PivotFields("Product").CurrentPage = s
        End With
    Else
        With Me.PivotTables
            Let s = .Item("PivotTable2").PivotFields("Product").CurrentPage
            .Item("PivotTable1").PivotFields("Product").ClearAllFilters
            .Item("PivotTable1").PivotFields("Product").CurrentPage = s
        End With
    End If

    Application.EnableEvents = True
 
End Sub

 

[chowyg]

I tried to use your formula. It says

Run-Time error '1004':
Unable to get the pivotfields property of the pivottable class

when i try to debug, it highlights this line
Let s = .Item("PivotTable1").PivotFields("Seat Segment").CurrentPage

 

[Greg Truby]

No, you probably can't use my syntax directly.

• How much experience to you have programming VBA?
  • Did you know how to put this code in the worksheet's code module?
  
  
• Are the pivot tables on one worksheet? Or two? Or three?
  • What are the names of the worksheet(s) involved

 

[chowyg]

How much experience to you have programming VBA?

I've been programming VBA for about fifteen minutes.

Did you know how to put this code in the worksheet's code module?

I think so, but I'm not sure.

Are the pivot tables on one worksheet? Or two? Or three?

So My worksheet labeled RawData and calculations has from left to right, my RawData, PivotTable1, PivotTable2 and PivotTable3 all on the same worksheet.

 

[Greg Truby]

OK - you're in Excel 2007. When you click on a pivottable, the contextual tabs for pivots become visible (they're probably dark red or magenta). On the first tab (Options), the very first item on the tab is PivotTable Name. We need to confirm that the tables are literally named PivotTable1, PivotTable2 & PivotTable3. Is that the case? If not, that's okay, just note the actual names and let us know them. Indeed it's not necessarily a bad idea to name them something that allows you to know which is which, something like pvtSales, pvtProduction, ...