pivot table question

[Mike1]

Is it possible to have 2 seperate pivot tables on the same excel worksheet link to the same report filter? I need two seperate pivot tables because one table contains data I need to exclude from the second, however it would be nice if the report filter sections linked to one another. That way if I make changes (apply certain filters) in the report filter section of the first pivot table, it would also automatically happen in the second so I don't have to just duplicate the filters on the second table. Any help is greatly appreciated, thanks!
Mike

 

[Jerry Sullivan]

Hi Mike,

This thread has some VBA functions that can be used together to have one or more PivotTables automatically update to synch to the filter changes of a "Master" PivotTable.

http://www.mrexcel.com/forum/showthread.php?t=595213

Just ask if you want help adapting it to your application.

 

[Mike1]

Thanks Jerry, I followed the instructions. I get it to work sometimes but not always. In my report filter I do have about 6 filters, so I added all 6 as sFields in the worksheet code. Do I need something different since I have multiple filters? I typically wasn't having problems when just changing one filter, but if I try to apply multiple filters, that is when it isn't carrying over to the new pivot table always. As I try to breainstorm other possible issues, I did re-name my titles of the filters and columns (some of them) by just typing over them in the pivot... can that be any issue?

Again, any help is greatly appreciated!
Mike

 

[Jerry Sullivan]

Mike,

Is it possible your 6 "filters" are PivotItems instead of PivotFields?

Just to clarify, the PivotField name is the name that appears in the PivotTable Field List pane in the section: "Choose fields to add to report:"

The PivotItems are the items that would each have their own checkbox for Visible/Not Visible when you filter a PivotField.

Check that out and let me know how many PivotFields you have.

If you have 6 PivotFields, then please post the calling Sub that you tried with the 6 PivotFields? No need to post the support functions that were in the link if you didn't change them.

 

[Mike1]

The 6 filters I am referring to are Pivot Fields. The calling sub is below. Again, when I remove all 6 sFields and just have one sField it works fine, but as I add more sFields, it quits working. Thanks for the help! Mike

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sMaster As String, sField As String

sMaster = "PivotTable1"
sField = "Region Number"
sField = "State"
sField = "BI Manager"
sField = "Account Exec"
sField = "BI Underwriter"
sField = "EG"


With ActiveSheet
If Intersect(Target, .PivotTables(sMaster) _
.TableRange2) Is Nothing Then Exit Sub

On Error GoTo CleanUp
Application.EnableEvents = False

Call Synch_All_PT_Filters_BasedOn( _
PT:=.PivotTables(sMaster), sField:=sField)
End With
CleanUp:
Application.EnableEvents = True
End Sub

 

[Jerry Sullivan]

Thanks Mike, That helps in understanding why this works for 1 field but not more than one.

When you assign each of the 6 values consecutively like this,
the last field is the only one that gets passed to the Synch_All_PT_Filters_BasedOn function.

Notes in green show the changes to the value of sField:

sMaster = "PivotTable1"

sField = "Region Number" 'assigns "Region Number" to variable sField
sField = "State" 'replaces "Region Number" with "State"
sField = "BI Manager" 'replaces "State" with "BI Manager"
sField = "Account Exec" '..ditto
sField = "BI Underwriter" '...ditto
sField = "EG" 'replaces Underwriter" with "EG"

At this point in the processing, sField="EG" ....all the other changes were irrelevant.
So as the code continues, "EG" will be the only field that gets synched.

What you need to do is in effect....

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMaster As String, sField As String
    sMaster = "PivotTable1"
    
    With ActiveSheet
        If Intersect(Target, .PivotTables(sMaster) _
            .TableRange2) Is Nothing Then Exit Sub
    
        On Error GoTo CleanUp
        Application.EnableEvents = False
    
        sField = "Region Number"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
    
        sField = "State"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
        
        sField = "BI Manager"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
        
        sField = "Account Exec"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
        
        sField = "BI Underwriter"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
        
        sField = "EG"
        Call Synch_All_PT_Filters_BasedOn( _
            PT:=.PivotTables(sMaster), sField:=sField)
    
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

That Sub could be shortened to step through an array list of fields,
but you might be better off keeping it simple like this.

Please let me know if the code works for you.

 

[Mike1]

That worked! As always, thanks for the help!

Mike

 

[RoryA]

FWIW, if you are using XL2010, you can control multiple pivots with a slicer.