I would like a macro so that If C10 on sheet 'Recon" is zero then filter on Pivot Table on Sheet "Purchases" must filtered to "Blank", otherwise filtered to Highest Year. I have set up a formula on sheet "purchases" to obtain the highest year from sheet "Raw Data"
I have tried to write code but cannot get it to work. It would be appreciated if someone could assist me
Sub Select_Pivot_Fields()
Dim pt As PivotTable
Dim pi As PivotItem
Sheets("purchases").Select
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable2")
pt.ManualUpdate = True
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Financial Year")
.PivotItems("(blank)").Visible = True
End With
'turn off all pivot table items except "blank"
For Each pi In pt.PivotFields("Financial Year").PivotItems
Select Case pi.Value
Case 1 To 5
pi.Visible = False
Case Else
pi.Visible = True
End Select
Next pi
pt.ManualUpdate = False
'turn on your desired selection of pivot table items
num = 1
For i = 1 To ActiveSheet.Range("J1")
mSelect1 = ActiveSheet.Range("J1" & num)
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Financial Year")
.PivotItems(mSelect1).Visible = True
.PivotItems("(blank)").Visible = False ' turn off blanks
End With
num = num
Next i
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
End Sub
Hi Howard,
Change your J1 formula to: =IF(Recon!C10=0,"(blank)",MAX('Raw Data'!M:M))
This way, the filter value we need is always in J1.
Try this code:
Dim pt As PivotTable
Dim pi As PivotItem
Sheets("purchases").Select
'On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable2")
pt.ManualUpdate = True
Dim FilterValue As String
FilterValue = Sheets("purchases").Range("J1")
'display first only
For Each pi In pt.PivotFields("Financial Year").PivotItems
If pi.Value = pt.PivotFields("Financial Year").PivotItems(1).Value Then
pi.Visible = True
Else
pi.Visible = False
End If
Next pi
'display the one we need (blank or highest)
pt.PivotFields("Financial Year").PivotItems(FilterValue).Visible = True
'hide first item if it's not the filtrvalue we need:
If pt.PivotFields("Financial Year").PivotItems(1).Value <> FilterValue Then
pt.PivotFields("Financial Year").PivotItems(1).Visible = False
End If
pt.ManualUpdate = False
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
Application.ScreenUpdating = True
End Sub
Thanks for the help-code works perfectly