On Thursday, November 29, 2012 11:26:53 AM UTC+1, wrote:
Hi all,



I am looking for code that clears all selections in a pivotfield. Pivottable compares values of current and previous yyear with budget. My goal is to always have only two years. namely the current and previous.

So far I have created:



sub twoyears()

curyear = Range("curyear").Value ' a cellreference having the value of this year

prevyear = Range("prevyear").Value ' a cellreference with value of 1 less



Sheets("Tur. YTD").Select

ActiveSheet.PivotTables("Tur. YTD").PivotFields("Region").ClearAllFilters

ActiveSheet.PivotTables("Tur. YTD").PivotFields("Region").CurrentPage = "All"

With ActiveSheet.PivotTables("Tur. YTD").PivotFields("Month")

.PivotItems("(blank)").Visible = False

End With



'this is he piece of code where i'm strugging with. when the for each loop has run three times, the column has no values and i get an error.

On Error Resume Next

For Each item In ActiveSheet.PivotTables("Tur. YTD").PivotFields("Year").PivotItems

item.Visible = False

Next

With ActiveSheet.PivotTables("Tur. YTD").PivotFields("Year")

.PivotItems(curyear).Visible = True

.PivotItems(prevyear).Visible = True

.PivotItems("budget").Visible = True

End With



end sub



Of course the 'resume next' solves the problem, but i don't think this is very elegant.

so my question is: what is the easiest way to clear selections in a pivotfieldcolumn



any help is greatly appreciated



greets,



sybolt