PDA

View Full Version : VBA Set PivotItem to Not Visible



smpggrl
01-15-2009, 09:16 AM
Hi, I have read as much as I can about this topic but anything I try is not working. If you look at my code (below), it seems like it should work but even though it compiles, it refuses to execute. I would appreciate any help you can give. Thanks!

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim strCri As String
Dim pos As Long
Set pt = Sheet5.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Deal Name")
strCri = "$000.00"
Application.ScreenUpdating = False
pt.ManualUpdate = True

With pf
.AutoSort xlManual, .SourceName
'On Error Resume Next
For Each pi In pf.PivotItems
pos = InStr(1, pi, strCri)
If pos = 0 Then
Else:
pi.Visible = False
MsgBox (pi)
End If
Next pi
.AutoSort xlAscending, .SourceName
End With

The message box shows the correct value that should be hidden, but it does not become hidden when I run the macro.

lucas
01-15-2009, 12:10 PM
Maybe if we had the workbook with the pivot table so we didn't have to create imaginary data and develop a pivot table to test it someone might take a look at this with you.

hit post reply and enter your message, scroll down and look for the button that says "manage attachements"

smpggrl
01-15-2009, 12:40 PM
Thanks...I have come up with an alternative solution though I still wonder why the first one was not working. I am on a time-sensitive project now but I will try to get some sample data up so we can understand this (just for fun!) at some time. I appreciate your response!