Help with code for selecting pivot table field by typing in a cell
 

I was given this code and have been messing around with it all day and have
not been able to figure out why it does not work. This code is on the same
sheet as the pivot table, and on the Immediate Window I
have:Application.EnableEvents = True the error I am getting when I type in
a value to find in my pivot table is:

Run-time error '1004':

Unable to set the Visible property of the PivotItem class

I have tried everything I can think of to make this work, does anyone have
any idea what i am doing wrong? thank you. here is the code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 1 Or Target.Column < 6 Then Exit Sub
Dim pi As PivotItem, pf As PivotField
Application.EnableEvents = False
Application.ScreenUpdating = False
' change name of Pivto Table and Pivot Field to your values on next
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("WOJO1")
With pf
pf.AutoSort xlManual, pf.SourceName

For Each pi In pf.PivotItems
pi.Visible = True
Next
For Each pi In pf.PivotItems
If pi.Name < Range("F1").Value Then
pi.Visible = False
End If
Next
pf.AutoSort xlAutomatic, pf.SourceName

End With

Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub

Help with code for selecting pivot table field by typing in a cell
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bContinue as Boolean
If Target.Row < 1 Or Target.Column < 6 Then Exit Sub
Dim pi As PivotItem, pf As PivotField
Application.EnableEvents = False
Application.ScreenUpdating = False
' change name of Pivto Table and Pivot Field to your values on next
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("WOJO1")
With pf
pf.AutoSort xlManual, pf.SourceName
bContinue = False
For Each pi In pf.PivotItems
pi.Visible = True
if pi.Name = Range("F1").Value then
bContinue = True
end if
Next
if bContinue = False then
msgbox "No Match - quitting"
Application.EnableEvents = True
Application.ScreenUpdating = True
exit sub
end if
For Each pi In pf.PivotItems
If pi.Name < Range("F1").Value Then
pi.Visible = False
End If
Next
pf.AutoSort xlAutomatic, pf.SourceName

End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Josh Johansen" wrote:

I was given this code and have been messing around with it all day and have
not been able to figure out why it does not work. This code is on the same
sheet as the pivot table, and on the Immediate Window I
have:Application.EnableEvents = True the error I am getting when I type in
a value to find in my pivot table is:

Run-time error '1004':

Unable to set the Visible property of the PivotItem class

I have tried everything I can think of to make this work, does anyone have
any idea what i am doing wrong? thank you. here is the code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 1 Or Target.Column < 6 Then Exit Sub
Dim pi As PivotItem, pf As PivotField
Application.EnableEvents = False
Application.ScreenUpdating = False
' change name of Pivto Table and Pivot Field to your values on next
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("WOJO1")
With pf
pf.AutoSort xlManual, pf.SourceName

For Each pi In pf.PivotItems
pi.Visible = True
Next
For Each pi In pf.PivotItems
If pi.Name < Range("F1").Value Then
pi.Visible = False
End If
Next
pf.AutoSort xlAutomatic, pf.SourceName

End With

Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub

Help with code for selecting pivot table field by typing in a
 

This didn't work either and I am still getting the same error. It is
highlighting:

pi.Visible = True

I have deleted it from the code for now because I have to present this in a
couple hours but I might try playing around with it again later, any ideas
whats wrong?

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bContinue as Boolean
If Target.Row < 1 Or Target.Column < 6 Then Exit Sub
Dim pi As PivotItem, pf As PivotField
Application.EnableEvents = False
Application.ScreenUpdating = False
' change name of Pivto Table and Pivot Field to your values on next
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("WOJO1")
With pf
pf.AutoSort xlManual, pf.SourceName
bContinue = False
For Each pi In pf.PivotItems
pi.Visible = True
if pi.Name = Range("F1").Value then
bContinue = True
end if
Next
if bContinue = False then
msgbox "No Match - quitting"
Application.EnableEvents = True
Application.ScreenUpdating = True
exit sub
end if
For Each pi In pf.PivotItems
If pi.Name < Range("F1").Value Then
pi.Visible = False
End If
Next
pf.AutoSort xlAutomatic, pf.SourceName

End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Josh Johansen" wrote:

I was given this code and have been messing around with it all day and have
not been able to figure out why it does not work. This code is on the same
sheet as the pivot table, and on the Immediate Window I
have:Application.EnableEvents = True the error I am getting when I type in
a value to find in my pivot table is:

Run-time error '1004':

Unable to set the Visible property of the PivotItem class

I have tried everything I can think of to make this work, does anyone have
any idea what i am doing wrong? thank you. here is the code?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 1 Or Target.Column < 6 Then Exit Sub
Dim pi As PivotItem, pf As PivotField
Application.EnableEvents = False
Application.ScreenUpdating = False
' change name of Pivto Table and Pivot Field to your values on next
Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields ("WOJO1")
With pf
pf.AutoSort xlManual, pf.SourceName

For Each pi In pf.PivotItems
pi.Visible = True
Next
For Each pi In pf.PivotItems
If pi.Name < Range("F1").Value Then
pi.Visible = False
End If
Next
pf.AutoSort xlAutomatic, pf.SourceName

End With

Application.ScreenUpdating = False
Application.EnableEvents = True
End Sub