Pivot table - Next Item vba button

[ianb]

Hi,


If a pivot table has the dates e.g.


Jan-12

Feb-12

Mar-12

etc

Dec-12

Jan-13


How can I via VBA button click and the pivot table can move to the next item.


Multi Clicks would goto next item until the end then back to the start.

Have searched and can not find any program on the internet on how to do this.


Thanks.

 

[ianb]

If any one is interested I have all the coding for the combo,listbox,dropdown boxes for usign a next button. also the programs for selection of now() in a pivot table.


Thanks.

 

[ianb]

Examples of how to do NOW () and START are listed below.


I am unable to do NEXT - e.g JAN-11 to JAN-12 in multi clicks ???


Can any one advise please. thanks.


ActiveSheet.PivotTables("PivotTable5").PivotFields("Resolved Month / Year"). _

CurrentPage = Format(Now, "mmm-yy")

ActiveSheet.PivotTables("PivotTable2").PivotFields("Resolved Date"). _

CurrentPage = Format(Now - 1, "dd/mm/yyyy")


ActiveSheet.PivotTables("PivotTable5").PivotFields("Resolved Month / Year"). _

CurrentPage = "Jan-11"

ActiveSheet.PivotTables("PivotTable2").PivotFields("Resolved Date"). _

CurrentPage = Format(Now - 31, "dd/mm/yyyy")

 

[ianb]

Here it is for the List Boxes. Can any one do this for the pivot tables. Items will move to the next in sequence. Thanks.


Sub UpdateOverviewNext()


Sheets("Dashboard").Select

Application.Goto Range("a1")

With Sheets("Dashboard")

.Select

With .DropDowns("Drop Down 1")

Do

If .ListIndex < .ListCount Then

.ListIndex = .ListIndex + 1

Else

.ListIndex = 1

End If

Debug.Print .ListIndex, .ListCount, .List(.ListIndex)

Loop While .List(.ListIndex) = ""

End With

End With

End Sub

 

[ianb]

Have also tried setting to a range and this did not work ;


= Range("D3").Value


Any advise or a program is most welcome. thanks.

 

[ianb]

Here it is folks if any one is interested.


Sub AdvancePivotTable()

Dim piD As PivotItems

Dim i As Integer

Dim iDMax As Integer


Set piD = ActiveSheet.PivotTables("PivotTable1").PivotFields("Dates").PivotItems

iDMax = piD.Count


For i = 1 To iDMax

If piD.Item(i).Visible Then

If i < iDMax Then

piD.Item(i + 1).Visible = True

Else

piD.Item(1).Visible = True

End If

piD.Item(i).Visible = False

End If

Next i


End Sub