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

hi sybolt,

Sub Macro1()
Dim criteria, pi As PivotItem
criteria = Array("curyear", "prevyear", "budget")
Application.ScreenUpdating = False
With ActiveSheet.PivotTables(1).PivotFields(1)
.ClearAllFilters
For Each pi In .PivotItems
If Not IsError(Application.Match(pi, criteria, 0)) Then
pi.Visible = True
Else
pi.Visible = False
End If
Next
End With
Application.ScreenUpdating = True
End Sub

--
isabelle

--
isabelle


Le 2012-11-29 05:26, a écrit :
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

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

Hi Isabella,

Thanks ever so much for your reply. I always seem to forget about arrays.
With a few adjustments I got the code running as I wanted.

The code is quicker and 'plus stabile'

Merci,

Sybolt