Hiding Pivot Table Items- VBA -An Excel Bug?

Posted by Andrew on May 04, 2001 4:25 AM

When I use a button to call the routine it breaks because it cannot find the items in the pivot table. However when I step through the code it works fine. Can anybody help?

---------------------------------
Sub ConfigueDates2()

Dim DateValueRemove As String
Dim EndWeek As String
Dim EndLastWeek As String

EndWeek = Range("EndWeek")
EndLastWeek = Range("EndLastWeek")

Sheets("Sheet2").Select

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date")

For x = 1 To .PivotItems.Count
DateValueRemove = .PivotItems(x).Name

If DateValueRemove = EndWeek Then GoTo NextDate
If DateValueRemove = EndLastWeek Then GoTo NextDate

.PivotItems.Item(DateValueRemove).Visible = False

NextDate:
Next
End With
End Sub

Posted by Dave Hawley on May 04, 2001 4:54 AM

Hi Andrew

If you have a button from the Control toolbox, set its TakeFocusOnClick Propery to False, or use "Range("A1").select" as the first line. A button from the Forms toolbar has less problems, but less flexibility.

Try your code with these bits added in, Might get a run time error or two, but I reckon you'll see what I'm getting at :o)

Sub ConfigueDates2()

Dim DateValueRemove As String
Dim EndWeek As String
Dim EndLastWeek As String
Dim pPT As PivotTable
EndWeek = Range("EndWeek")
EndLastWeek = Range("EndLastWeek")
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Set pPT = ActiveSheet.PivotTables("PivotTable3")

With pPT
.ManualUpdate = True

For x = 1 To .PivotFields("Date").PivotItems.Count
DateValueRemove = .PivotItems(x).Name

If DateValueRemove = EndWeek Then GoTo NextDate
If DateValueRemove = EndLastWeek Then GoTo NextDate

.PivotItems.Item(DateValueRemove).Visible = False

NextDate:
Next

.ManualUpdate = False

End With
Set pPT = Nothing
Application.ScreenUpdating = False
End Sub

The ".ManualUpdate = " should speed things up considerably.

Dave
OzGrid Business Applications