0

I'm trying to set up a macro to filter a pivot table based on a user defined range.

So far all I have is the prompt to get the range. Unfortunately, I have no idea how to take the values from the range and apply it to the filter. Thanks for your help!

Sub foreachloop()

  Dim wbmodel As Excel.Workbook
Set wbmodel = ActiveWorkbook

Dim rng As Range
Dim Filter As String




  With wbmodel.Sheets("Pivot_model").PivotTables("Modelpivot")

    .ClearAllFilters

     Set rng = Application.InputBox("select Range", "Inputs", Type:=8)
     Filter = Application.InputBox("select Filter", "Inputs", Type:=8)

     MsgBox rng.Address
     MsgBox Filter


    For Each PivotItem In .PivotFields(Filter).PivotItems
    Select Case PivotItem.Name
        Case rng
            PivotItem.Visible = True
        Case Else
            PivotItem.Visible = False
        End Select
    Next PivotItem


  End With

  End Sub

Ok. so I made some (slight) updates. I made my first "For Each" loop. Now, I know I will need to do a similar "for each" loop to apply the filter. I've been battling with the code for a few hours and I am unsure where to go next.

VBArookie
  • 25
  • 7

1 Answers1

0

Try this Filter Excel pivot table using VBA

You have to cycle through your range and make the pivot item visible, everything else hidden.

Community
  • 1
  • 1
gemmo
  • 1,286
  • 2
  • 16
  • 33
  • I'm sue that post has exactly what I need, but to be completely honest I need an interpreter to decipher the solution code for me. I believe i'm on the right path with my current code. I am just not sure how to cycle through the "rng".... can you help me with this last section? – VBArookie May 15 '17 at 16:44
  • i'm thinking the reason my code doesn't work is because I had the user input a range. I now need to cycle through the values within the range. I might be completely off base, and there may be an easier way to do this. – VBArookie May 15 '17 at 17:00
  • You need a variable to assign. So you have assigned "rng" as your variable. But you need to set your the value for rng. like ... rng = cells(n+1,1).value https://msdn.microsoft.com/en-us/library/office/ff196273.aspx – gemmo May 17 '17 at 22:36