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

 

[mzajac]

Hi,


I am self-learning and completely new to VBA and I am seeking for help and advice.
I want to use two dependent data validation fields to control two pivot tables filters in the same worksheet (screenshot below). The slicer option isn't the best idea due to the huge number of teams (and teams have long names) so the slicer is bigger than the chart itself.

I tried adapting codes posed in this thread but my skills are yet insufficient




I found the below code on the contextures website, adapted to my needs but I am able to control only "Region" filters in both PivotTables, but whenever I try to adapt this one to be able to control "Team" filter, I am failing.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String


strField = "Region"


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
    If Target.Address = Range("E3").Address Then


        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Can anyone help me with the aforementioned problem?
I use Excel 2013.


Kind regards,
MZajac

 

[baldmosher]

Hi, bringing up an old thread, is it possible to have Target.Value (in the Worksheet_Change event) pick up multiple items to be selected in the pivot table(s)?

For example, rather than my data validation dropdown being 1 item to select in all pivots, it would be a reference to another list of multiple items relating to that first item. So I could select "all Ice Cream" in a validation list of desserts, and I would have a list of "all Ice Creams" on another sheet, containing "Vanilla", "Chocolate", "Strawberry". The macro would need to select those three individual ice creams in the "Desserts" PivotField.

I've got my code and lookups ready, all I need to know now is whether this will even work... and if it will work, what do I need to pass to the subroutine, instead of Target.Value?
- I tried selecting multiple cells as Target, no luck, it seems to pass and recognise Target as an array, recognises LBound = 1 and UBound = 3, but doesn't seem to recognise the items in the array?
- I tried changing from Target.Value to Target, again no luck
- I tried using VisibleItemsList, no luck

I should add that playing with Arrays built from sheets is something I've been trying to do for some time and so far I've failed to understand it completely!

I managed to do this. Worked quite well. But was painfully slow with slicers. I can post my code if anyone wants it.

 

[baldmosher]

Hi,


I am self-learning and completely new to VBA and I am seeking for help and advice.
I want to use two dependent data validation fields to control two pivot tables filters in the same worksheet (screenshot below). The slicer option isn't the best idea due to the huge number of teams (and teams have long names) so the slicer is bigger than the chart itself.

I tried adapting codes posed in this thread but my skills are yet insufficient




I found the below code on the contextures website, adapted to my needs but I am able to control only "Region" filters in both PivotTables, but whenever I try to adapt this one to be able to control "Team" filter, I am failing.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String


strField = "Region"


On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
    If Target.Address = Range("E3").Address Then


        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If


Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Can anyone help me with the aforementioned problem?
I use Excel 2013.


Kind regards,
MZajac

Presumably you just want two separate change events, one for each entry box. So just repeat the code again with the other field name.

Or you could create separate subs so you can pass the Target.Value and "Region" or "Team" as strings, depending on which data entry cell is changed.

 

[SURFER349]

Hello,

Is this thread still active? I'd love to get some help with a few more similar situations.

My situation doesn't have DV or selectable user inputs. I've got a main front page where the user inputs a few specific terms and I'd like this code to then update pivottables on several other tabs that are hidden in the background.

User Input Cells: B2, B3 on sheet = "MAIN" --> This reference value will be an name.

PT's on sheets in the background, "Sheet1" Sheet2" "Sheet3" etc, each w/ PivotTable1, PivotTable2, etc
I set up the PivotTable to 'report filter' on "NAME"

 

[Jerry Sullivan]

Hello,

Is this thread still active? I'd love to get some help with a few more similar situations.

My situation doesn't have DV or selectable user inputs. I've got a main front page where the user inputs a few specific terms and I'd like this code to then update pivottables on several other tabs that are hidden in the background.

User Input Cells: B2, B3 on sheet = "MAIN" --> This reference value will be an name.

PT's on sheets in the background, "Sheet1" Sheet2" "Sheet3" etc, each w/ PivotTable1, PivotTable2, etc
I set up the PivotTable to 'report filter' on "NAME"

Hi SURFER349, Since this thread is rather old and lengthy, I'll respond to the thread you started on this topic yesterday.

https://www.mrexcel.com/forum/excel...off-multiple-reference-cells.html#post4762834

 

[wildturkey]

Somehow I've managed to get this to work perfectly, and my pivot updates to changes made to a cell - it works with everything apart from dates - any ideas please? I'm currently using the following in the pivot worksheet...

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

And the following in the worksheet module...

Public Function Filter_PivotField(pvtField As PivotField, _
        vItems As Variant)
'---Filters the PivotField to make stored vItems Visible
    Dim sItem As String, bTemp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    If Not (IsArray(vItems)) Then
         vItems = Array(vItems)
    End If

    With pvtField
        .Parent.ManualUpdate = True
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        If vItems(0) = "(All)" Then
            For i = 1 To .PivotItems.Count
                If Not .PivotItems(i).Visible Then _
                    .PivotItems(i).Visible = True
            Next i
        Else
            For i = LBound(vItems) To UBound(vItems)
                bTemp = Not (IsError(.PivotItems(vItems(i)).Visible))
                If bTemp Then
                    sItem = .PivotItems(vItems(i))
                    Exit For
                End If
            Next i
            If sItem = "" Then
                MsgBox "None of filter list items found."
                GoTo CleanUp
            End If
            .PivotItems(sItem).Visible = True
            For i = 1 To .PivotItems.Count
                If IsError(Application.Match(.PivotItems(i), _
                    vItems, 0)) = .PivotItems(i).Visible Then
                    .PivotItems(i).Visible = Not (.PivotItems(i).Visible)
                End If
            Next i
        End If
    End With
   
CleanUp:
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function