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

 

[jnerga]

Hello Jerry,

I want to and this DV dropdown in C2 in addition to the two others (a2 and b2).b48 and b 76 are the cells in the two PivotTables..The names the for cells are STC in both PivotTables.The PivotTable names are still PivotTable1 and PivotTable 2... Many thanks for help...brgds Jorgen

 

[Jerry Sullivan]

Hello Jerry,

I want to and this DV dropdown in C2 in addition to the two others (a2 and b2).b48 and b 76 are the cells in the two PivotTables..The names the for cells are STC in both PivotTables.The PivotTable names are still PivotTable1 and PivotTable 2... Many thanks for help...brgds Jorgen

Are you saying STC is the PivotField name?

In the previous code, we made it so that if either A2 or B2 was changed, both fields of both tables are updated to the current values of A2 and B2.
We can extend this approach so that if either A2, B2, or C2 are changed, all 3 fields of both tables are updated to the current values of A2, B2 and C2.

An alternative approach would be to only update the field affected by A2 when A2 is changed, B2 when B2 is changed, and C2 when C2 is changed.

The first approach will ensure your PivotTables are consistent with A2, B2 and C2.
The second approach is more efficient (faster), but if someone manually filters the PivotTable, instead of using the 3 dropdowns, you could get a misleading result in which the PivotTable displayed doesn't match all 3 dropdowns.

Which would be better for you (Option 1-Consistency or Option 2-Speed)?

 

[jnerga]

Yes, STC is the PivotField name. I think the first approach would be best.. to ensure the PivotTables are consistent with A2, B2 and C2.

/Jorgen

 

[Jerry Sullivan]

I don't have your workbook mocked up, so please 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
    Dim uField As String, uDV_Address As String
    Dim vPT_Names As Variant, i As Long
    
    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.
    uField = "STC" 'Field Name
    uDV_Address = "$C$2" 'Cell with DV dropdown to select filter item.
     
    If Intersect(Target, Range(sDV_Address & "," & tDV_Address & "," _
        & uDV_Address)) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
     
    On Error GoTo CleanUp
    Application.EnableEvents = False
     
    vPT_Names = Array("PivotTable1", "PivotTable2")
    For i = LBound(vPT_Names) To UBound(vPT_Names)
        With Sheet1.PivotTables(vPT_Names(i))
            Call Filter_PivotField( _
                pvtField:=.PivotFields(sField), _
                vItems:=Range(sDV_Address).Value)
            Call Filter_PivotField( _
                pvtField:=.PivotFields(tField), _
                vItems:=Range(tDV_Address).Value)
            Call Filter_PivotField( _
                pvtField:=.PivotFields(uField), _
                vItems:=Range(uDV_Address).Value)
        End With
    Next i
     
CleanUp:
    Application.EnableEvents = True
End Sub

 

[Jerry Sullivan]

Jorgen, I spotted and fixed a few typos after posting. Please refresh this thread and use the corrected version.

 

[jnerga]

WOW Jerry! This works perfect!!! Many many thanks!! Have a great day!!! Brgds Jorgen

 

[jnerga]

Just a quick question, Jerry. How can I mark all selections in the STC PivotField for both pivots? I have tried to add (all) in the datavalidation field (C2), but it does seem to work..

 

[Jerry Sullivan]

Just a quick question, Jerry. How can I mark all selections in the STC PivotField for both pivots? I have tried to add (all) in the datavalidation field (C2), but it does seem to work..

Jorgen,

The function should make all PivotItems Visible if your data validation list has the value: (All)

This is case-sensitive, so if you used (all) it would return an "Item not found" error message.

The intent of this syntax was to match the selection of (All) when using PageField filters.

 

[jnerga]

Thanks again Jerry! Works fine!!

 

[jnerga]

Hello Jerry,

Possible to use the same script to change 10 pivotables in 5 different sheets based on same input values?

Help is much appreciated. Brgds Jorgen