Select PivotItems from to...

[bremen_]

Hi everyone!

I have an pivottable with pivofields ("Year").

Via a UserForm I chose the desired period using 2 Comboboxes: Combobox1 (from) and Combobox2 (to).

Depending on the selected period, e.g. from 2011 to 2013, i want to select all of the years in the pivot table in that period (2011, 2012, 2013).

I came up with a code, it makes something, but not that what i want... Right now the code selects the pivoitems 2,3,4 (but manually, with fix values in the code) but i want it to select them depending on the variable in the combobox.

Sub pivot_item_jahr()

Dim oPI As PivotItem
Dim i As Integer
For Each oPI In ActiveSheet.PivotTables("PivotTable2").PivotFields("Year").PivotItems
For i = 2 To 4
ActiveSheet.PivotTables("PivotTable2").PivotFields("Year").PivotItems(i).Visible = True
Next i
oPI.Visible = False
Next oPI
End Sub

Thanks a lot for any help!

[mana]

Sub test()
Dim d1 As String
Dim d2 As String

d1 = "1/1/" & 2011 'combobox1
d2 = "12/31/" & 2013 'combobox2

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.ClearAllFilters
.PivotFilters.Add _
Type:=xlDateBetween, _
Value1:=d1, _
Value2:=d2
End With

End Sub


Sub test2()
Dim y1 As String
Dim y2 As String

y1 = 2011 'combobox1
y2 = 2013 'combobox2

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Year")
.ClearAllFilters
.PivotFilters.Add _
Type:=xlCaptionIsBetween, _
Value1:=y1, _
Value2:=y2
End With

End Sub