Excel 2003 Pivot Table Page Field <> "(All)"

[rickincanada]

Hi there,

I'm attempting to hide a column on a Pivot Table in an Excel 2003 workbook whenever the a couple of the Page Fields don't = "(All)". Here's what I've got so far however I'm sure at first glance you'll see some of my issues. Can someone please help!!

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.ScreenUpdating = False
If Target.PageField("Type").PivotItems.Value = "(All)" _
And Target.PageField("Time Type").PivotItems.Value = "(All)" _
Me.Cells(1, 4).EntireColumn.Hidden = False
Else
Me.Cells(1, 4).EntireColumn.Hidden = True
End If
Dim z As PivotField
Application.ScreenUpdating = True
End Sub

Thanks so much!

Rick

 

[Marcelo Branco]

Hi,

Maybe

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Application.ScreenUpdating = False
    If Target.PivotFields("Type").CurrentPage <> "(All)" _
        And Target.PivotFields("Time type").CurrentPage <> "(All)" Then
        Me.Cells(1, 4).EntireColumn.Hidden = True
    Else
        Me.Cells(1, 4).EntireColumn.Hidden = False
    End If
    Dim z As PivotField
    Application.ScreenUpdating = True
End Sub

HTH

M.

 

[rickincanada]

You were close...

Here's what I ended up with:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Application.ScreenUpdating = False
If Target.PageFields("Type").CurrentPage.Value = "(All)" _
And Target.PageFields("Time Type").CurrentPage.Value = "(All)" Then
Me.Cells(1, 4).EntireColumn.Hidden = False
Else
Me.Cells(1, 4).EntireColumn.Hidden = True
End If
Dim z As PivotField
Application.ScreenUpdating = True
End Sub

And it works perfect!

Thanks for your help!

 

[Marcelo Branco]

You are welcome.

Glad for help

M.