I want to create a Year to date button for a pivot table

A

Aaron

My button has this code now to control the Page feild of the PT:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Month")
.PivotItems("9/30/2006").Visible = False
.PivotItems("10/31/2006").Visible = False
.PivotItems("11/30/2006").Visible = False
.PivotItems("12/31/2006").Visible = False
End With

Thus leaving only the 2007 months visable, thus creating a YTD view. But
when the year flips to 2008 I want the code to automatically Hide all 12
months in 2007 as well. I was thinking maybe I could set all pivotitems in
pivotfeild Month to false if < cell A1 which I could set a formula in. What
would this code look like, or is there a better way?
 
T

Tom Ogilvy

Sub HideItems()
Dim pi As PivotItem
Dim pf As PivotField
Dim s As String, dt As Date
With ActiveSheet.PivotTables("PivotTable2")
.ManualUpdate = True
Set pf = .PivotFields("Month")
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
s = pi.Name
dt = CDate(s)
If Year(dt) < Year(Date) Then
pi.Visible = False
End If
Next
pf.AutoSort xlAscending, pf.SourceName
.ManualUpdate = False
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top