update pivot dynamically based on range in separate worksheet

[Nmarkit]

Hello

I have a pivot table with client name, volume, product and dates.

On a separate worksheet i have a list of products defined in a range - "product". this list changes depending on the selection made by the user from a drop down list. what i need is that depending on the values within this range the pivot table filters the products. Can someone please provide the vba code to do this? attached is a small data sample

Thanks!

[p45cal]

For other's information:
cross posts
http://www.mrexcel.com/forum/excel-q...worksheet.html
http://www.excelforum.com/excel-char...worksheet.html

Nmarkit, some light reading:
http://www.excelguru.ca/content.php?...-cross-posters
http://www.excelfox.com/forum/f25/me...-posters-1172/

I can't see a drop down list anyhere in your workbook apart from on the pivot table itself - is this what you mean?
Do you want to create a dropdown list? if so would a slicer do?
If so, when the pivot table is selected, the ribbon includes a Pivot Table Tools section, therein you'll see a Sort & Filter section and there you'll see Insert Slicer, click that, click the TYPE checkbox, OK, then you can put the slicer wherever you like, even on another sheet, you can also adjust how it looks etc. Is that what you're after?

[Nmarkit]

The file attached is a sample one. I didn;t create a drop down in it. I put the output of the drop down selection on Sheet3 as I would need the pivot to be filtered on this output. Here's the code that is getting the selection from the listbox and dumping it on the separate worksheet. output is the name of the worksheet that the products are being dumped in.

Private Sub ListBox1_LostFocus()
    Dim listItems As String, i As Long
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i) & ", "
        Next i
    End With
    
    If Len(listItems) > 0 Then
        Sheets("output").Range("J1") = Left(listItems, Len(listItems) - 2)
    Else
        Sheets("output").Range("J1") = ""
    End If
    Range("BE2:BE100").ClearContents
    SplitAll
End Sub

Sub SplitAll()
    Dim src As Range
    Dim result As Variant
    For Each src In Sheets("output").Range("J:J").SpecialCells(xlCellTypeConstants)
      result = Split(src, ",")
      
        'last cell in column C
        With Cells(1, 57).End(xlUp)
            Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
        End With
    Next src
End Sub

thanks for the reading material. will go through it now.

[Nmarkit]

Apologies, my first time on forums so wasn;t aware on rules regarding cross posting. I have add the cross post details to the mrexcel forum. Not able to access excelforum for some reason but will update my post there as well once I;m able to get in. Thanks for your help with my query.

[Nmarkit]

does the above code make sense? do you need any more details to figure this out?

[p45cal]

Try this macro in the workbook you provided, it uses the list on sheet3 to set what's showing in the pivot table in Sheet1.

Sub blah()
Set PT = Sheets("Sheet1").PivotTables("PivotTable3")
Set ccc = PT.PivotFields("TYPE").PivotItems
PT.ManualUpdate = True
ccc(ccc.Count).Visible = True  'at least one must be visible at all times, so choose the last one.
Set TypeToShow = Sheets("Sheet3").Range("A1")  'would this be BE2 in your workbook?
Set TypeToShowStart = TypeToShow
Do Until IsEmpty(TypeToShow.Offset(1))
  Set TypeToShow = TypeToShow.Offset(1)
Loop
Set showlist = Range(TypeToShowStart, TypeToShow)
For Each itm In ccc
  itm.Visible = Not IsError(Application.Match(itm.Name, showlist, 0))
Next itm
PT.ManualUpdate = False
End Sub

if the list is blank it will fall over.

[Nmarkit]

thanks works perfectly to update my pivot! thanks a lot

just one small issue though. if I select client A and then select product 1 and product 2 from the list then i get the updated graph. However if i now sleect client B from the drop down my pivot doesn;t refresh to "select all" so i have only product 1 and product 2 selected. so even if client B has product 3 available, the pivot doesn;t select it. Anything i can add so that my pivot refeshes everytime i select a new client from filter?

[Nmarkit]

this is how my combobox is currently setup

Private Sub ComboBox1_Change()
    Sheets("Dashboard").Range("E9").Value = Sheets("Dashboard").ComboBox1.Value
    Sheets("PivotTrade").Range("B4") = Sheets("Dashboard").Range("E9").Value
    Sheets("PivotAccount").Range("B1") = Sheets("Dashboard").Range("E9").Value
    Sheets("PivotRevenue").Range("B2") = Sheets("Dashboard").Range("E9").Value
End Sub

[p45cal]

Then you'll need something along the lines of:
ActiveSheet.PivotTables("PivotTable3").PivotFields("TYPE").ClearAllFilters
(or PT.PivotFields("TYPE").ClearAllFilters in my code) somewhere when the user changes the client.

[Nmarkit]

thank you thank you thank you