Pivot table with data validation

[vinwin06]

HI,

I required vba codings for pivot table but the value should be choose from the data validation i have created.

For example : I have two pivot tables in sheet1 and these two pivot table have a common column filed available so i have created the data validation in cell D6. Now i need to give reference to the cell D6 for both the pivot table on the column labels.

Based on the value selected on the drop down the pivot table need to be changed on the both pivot tables.

Regards,

VInwin

 

[Jerry Sullivan]

Hi js411, tanks for fast answer!! Yes, I want to use values for A12 and B12 to be applied as filters to both PivotTables...help would be most appreciated. Brgds

You could try this untested code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sField As String, sDV_Address As String
    Dim tField As String, tDV_Address As String
   
    sField = "From_Name"  'Field Name
    sDV_Address = "$A$12" 'Cell with DV dropdown to select filter item.
    tField = "To_Name"  'Field Name
    tDV_Address = "$B$12" 'Cell with DV dropdown to select filter item.
    
    With ActiveSheet
        If Intersect(Target, Range(sDV_Address & "," & tDV_Address)) _
            Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
        
        On Error GoTo CleanUp
        Application.EnableEvents = False
    
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable1").PivotFields(sField), _
                vItems:=Target.Value)
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable2").PivotFields(sField), _
                vItems:=Target.Value)
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable1").PivotFields(tField), _
                vItems:=Target.Value)
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable2").PivotFields(tField), _
                vItems:=Target.Value)
    End With
       
CleanUp:
    Application.EnableEvents = True
End Sub

If there were many more PivotTables or Fields to filter, you could make a loop instead of copying the Calls repetitively.

 

[jnerga]

The relationship is that PivotTable 1 is showing from_name and to_name in first and second column. PiovotTable 2 is showing to_name first and from_name as second (Opposite)...

 

[Jerry Sullivan]

The relationship is that PivotTable 1 is showing from_name and to_name in first and second column. PiovotTable 2 is showing to_name first and from_name as second (Opposite)...

The position of the fields shouldn't make a difference for the coding.

 

[jnerga]

Hello again! Tried the new script. All 2 filters (in both pivotable 1 and 2) collect data from $A$12 when updating $A$12. When updating $B$12 only filter From_Name (in both pivotable 1 and 2) where updated... Tricky this one... Thanks again!

 

[Jerry Sullivan]

Hello again! Tried the new script. All 2 filters (in both pivotable 1 and 2) collect data from $A$12 when updating $A$12. When updating $B$12 only filter From_Name (in both pivotable 1 and 2) where updated... Tricky this one... Thanks again!

Oops, this always happens when I post code without testing it.

Please try replacing this part with the code below.

    Call Filter_PivotField( _
        pvtField:=.PivotTables("PivotTable1").PivotFields(sField), _
            vItems:=Range(sDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=.PivotTables("PivotTable2").PivotFields(sField), _
           vItems:=Range(sDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=.PivotTables("PivotTable1").PivotFields(tField), _
           vItems:=Range(tDV_Address).Value)
    Call Filter_PivotField( _
        pvtField:=.PivotTables("PivotTable2").PivotFields(tField), _
           vItems:=Range(tDV_Address).Value)

 

[jnerga]

Works great, Jerry!! Many thanks!!!
Do you know if it's possible to "move" the dropdown cells to another sheet.example sheet2. I have a few graphs there. Would be much easyer to
"choose" data from the graph sheet... Do I need to change the code?
Brgds Jorgen

 

[Jerry Sullivan]

Works great, Jerry!! Many thanks!!!
Do you know if it's possible to "move" the dropdown cells to another sheet.example sheet2. I have a few graphs there. Would be much easyer to
"choose" data from the graph sheet... Do I need to change the code?
Brgds Jorgen

Jorgen,

The DV Cells don't have to be on the same sheet as the PivotTables; however you'll need to make a few edits.
1. The Worksheet_Change code should be moved to the same sheet as the DV Cells; and
2. The references to the PivotTables must be qualified with the SheetName.

The revised code might look like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sField As String, sDV_Address As String
    Dim tField As String, tDV_Address As String
   
    sField = "From_Name"  'Field Name
    sDV_Address = "$A$12" 'Cell with DV dropdown to select filter item.
    tField = "To_Name"  'Field Name
    tDV_Address = "$B$12" 'Cell with DV dropdown to select filter item.
    
    If Intersect(Target, Range(sDV_Address & "," & tDV_Address)) _
            Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
        
    On Error GoTo CleanUp
    Application.EnableEvents = False

    With Sheets("MyPivots") 'Sheet with PivotTables
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable1").PivotFields(sField), _
               vItems:=Range(sDV_Address).Value)
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable2").PivotFields(sField), _
                vItems:=Target.Value)
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable1").PivotFields(tField), _
                vItems:=Target.Value)
        Call Filter_PivotField( _
            pvtField:=.PivotTables("PivotTable2").PivotFields(tField), _
                vItems:=Target.Value)
    End With
       
CleanUp:
    Application.EnableEvents = True
End Sub

 

[jnerga]

Great Jerry!! Many thanks again!!

 

[jnerga]

Hello again,

Hope you can help me with this...

I have a report filter in both pivottables with same value(s) - only 2 values. Possible to add one dropdown (datavalidation) to change both pivot tables at the same time?
The cells are B48 and B76...Dropdown validation : C2. Fieldname is STC..

Many thanks for help. Brgds Jorgen

<QUOUTE>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sField As String, sDV_Address As String
Dim tField As String, tDV_Address As String
 
sField = "From_Name" 'Field Name
sDV_Address = "$A$2" 'Cell with DV dropdown to select filter item.
tField = "To_Name" 'Field Name
tDV_Address = "$B$2" 'Cell with DV dropdown to select filter item.
 
With ActiveSheet
If Intersect(Target, Range(sDV_Address & "," & tDV_Address)) _
Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
 
On Error GoTo CleanUp
Application.EnableEvents = False
 
Call Filter_PivotField( _
pvtField:=Sheet1.PivotTables("PivotTable1").PivotFields(sField), _
vItems:=Range(sDV_Address).Value)
Call Filter_PivotField( _
pvtField:=Sheet1.PivotTables("PivotTable1").PivotFields(tField), _
vItems:=Range(tDV_Address).Value)
Call Filter_PivotField( _
pvtField:=Sheet1.PivotTables("PivotTable2").PivotFields(sField), _
vItems:=Range(tDV_Address).Value)
Call Filter_PivotField( _
pvtField:=Sheet1.PivotTables("PivotTable2").PivotFields(tField), _
vItems:=Range(sDV_Address).Value)
End With
 
CleanUp:
Application.EnableEvents = True
End Sub

</QUOUTE>

 

[Jerry Sullivan]

Hello again,

Hope you can help me with this...

I have a report filter in both pivottables with same value(s) - only 2 values. Possible to add one dropdown (datavalidation) to change both pivot tables at the same time?
The cells are B48 and B76...Dropdown validation : C2. Fieldname is STC..

Hi Jorgen,

Are you wanting to add this DV dropdown/ filter at C2 in addition to the ones you have at A1, B1, or instead of those?

Are cells B48 and B76 the addresses of the two PivotTables?
I'd suggest you use their names so this will continue to work if you move the PivotTables on the Sheet. What are the two PT names?