pivot table question

[olympiac]

I have a huge amount of data filtered in a pivot table. The xample below is simplified to understand the feasibility of my request.
Heading:name Heading:animal
name 1 cat
name 2 cat
name 3 cat
name 4 cat
name 5 cat
name 6 cat
name 7 dog
name 8 dog
name 9 dog
name 10 dog

Is it possible to filter the pivot table by "cat" (column label) and keep all the 10 names (Raw label)? From name 7 to 10 I need empty value in this example.
Is it feasible?

 

[Jerry Sullivan]

Hi John,

This synch filters code evolved a bit in this more recent thread...
http://www.mrexcel.com/forum/showthread.php?t=595213

I've broken it up into a function that saves filters for a specified field, and another that applies those saved filters to one or more Pivot Tables.

I can help you with a front-end calling Sub if you'll give me some details.

1. What are the names of the 4 fields?

2. Describe the relationship of the multiple PivotTables (are they "all the tables on SheetX, or spread out on several sheets-then provide sheet names, pivotTable names).

3. Which is the Master PT? (SheetName and PivotTable Name).

 

[jholme9]

Thank you so much for your quick reply and your help! Here is the information:

1. What are the names of the 4 fields?
Landline
Wireless
Home Security
Price Lock

2. Describe the relationship of the multiple PivotTables (are they "all the tables on SheetX, or spread out on several sheets-then provide sheet names, pivotTable names).
The two PivotTables are on the same sheet, "MarketSimulator". The Pivot Tables are simply "PivotTable3" and "PivotTable4."

3. Which is the Master PT? (SheetName and PivotTable Name).
PivotTable3 in the MarketSimulator sheet is the Master.

Thanks again for your help with this. I really appreciate it. I'll look through the other thread you referenced to see if I can figure anything out on my own.

John

 

[Jerry Sullivan]

John,

You could try the code below on a copy of your workbook.
I've only done a little bit of testing just now using the multiple fields,
but it seems to work.

Follow all the instructions listed in this thread (Post #2)
http://www.mrexcel.com/forum/showthread.php?t=595213

Except, when you get to Instruction 4. Paste this Worksheet_Change code into your
sheet code module of Sheet MarketSimulator instead of the Worksheet_Change code shown in that link.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sMaster As String
    Dim vFields As Variant
    Dim i As Long
    
    sMaster = "PivotTable3"
    vFields = Array("Landline", "Wireless", _
                    "Home Security", "Price Lock")
    
    With ActiveSheet
        If Intersect(Target, .PivotTables(sMaster) _
            .TableRange2) Is Nothing Then Exit Sub
        
        On Error GoTo CleanUp
        Application.EnableEvents = False
    
        For i = LBound(vFields) To UBound(vFields)
            Call Synch_All_PT_Filters_BasedOn( _
                PT:=.PivotTables(sMaster), _
                sField:=CStr(vFields(i)))
        Next i
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

 

[jholme9]

Thanks again, Jerry. I am truly grateful.

My VPN is currently giving me issues so I'm going to try to implement in the morning. I'll report back tomorrow.

 

[Jerry Sullivan]

Glad to help John. Just let me know if you have any difficulties.

 

[jholme9]

That worked flawlessly! Thanks again - you and this forum are awesome!