Pivot Table Macro issue

[natetc]

I'm working on a pivot table macro that filters certain fields in the macro. However, whenever that field doesn't populate the macro breaks.

Here is an example, I have no 'email' counts in my data and therefore the debugger stops at this point in the formula.

Sheets("Call Work Code").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Call Work Code")
.PivotItems("EMAIL").Visible = False
.PivotItems("FAX").Visible = False
End With

How can I plug in an if statement so that if it is not applicable move onto the next field?

Thanks in advance!

 

[natetc]

Have I given enough information? I figure its a simple 'else if' statement.

 

[Jerry Sullivan]

Hello natec,

Here's a function that you can call to filter a single PivotField
to show only the items that are listed in an Item List.

The function is called using two required parameters:

pvtField: The PivotField to be filtered
varItemList: A Variant Array of the items to be Visible

The varItemList parameter can be either an array or a range.

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)

    Dim strItem1 As String, blTmp As Boolean, i As Long
    On Error Resume Next
    Application.ScreenUpdating = False
         
    If Not (IsArray(varItemList)) Then
         varItemList = Array(varItemList)
    End If
 
    With pvtField
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        For i = LBound(varItemList) To UBound(varItemList)
            blTmp = Not (IsError(.PivotItems(varItemList(i)).Visible))
            If blTmp Then
                strItem1 = .PivotItems(varItemList(i))
                Exit For
            End If
        Next i
        If strItem1 = "" Then
            MsgBox "None of filter list items found."
            Exit Function
        End If
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
End Function

Below are two examples showing how to call the function.

The first uses a hard-coded array for the items.

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Locations"), _
        varItemList:=Array("San Diego", "Boston", "Chicago")
End Sub

The second example gets the Item list from a named range

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Locations"), _
        varItemList:=Application.Transpose(Sheets("Sheet2").Range("Cities"))
End Sub

Hope this helps!

 

[natetc]

Hi Jerry,

Thanks so much for your help.

My criteria is to select only 'email' and 'fax' items out of the "Call work code" list. So if they are not avaialble the pivot table should not show anything.

Sorry hope that makes sense, I think the code you were showing me selects all items underneath the "Call work code" title without filters.

Thanks in advance for your response.

 

[Jerry Sullivan]

My criteria is to select only 'email' and 'fax' items out of the "Call work code" list. So if they are not avaialble the pivot table should not show anything.

Please clarify...
Do you want:
"email" and "fax" items to Visible (and all other items Hidden)
or
"email" and "fax" items to Hidden (and all other items Visible)
or something else?

 

[natetc]

I only want "email" and "fax" items to Visible (and all other items Hidden)

Is there any way you can help me input this into my code? This way I can figure out what I'm doing wrong as I tried to input your code and got the debugger.

Sheets("Call Work Code").Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Call Work Code")
.PivotItems("EMAIL").Visible = False
.PivotItems("FAX").Visible = False
End With
Sheets("User.Call Work Code").Select
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Call Work Code")
.PivotItems("EMAIL").Visible = False
.PivotItems("FAX").Visible = False
End With
Sheets("Dealer.Call Work Code").Select
With ActiveSheet.PivotTables("PivotTable5").PivotFields("Call Work Code")
.PivotItems("EMAIL").Visible = False
.PivotItems("FAX").Visible = False
End With

Thanks!

 

[Jerry Sullivan]

Are these three PivotTables PivotTable2, PivotTable3, PivotTable4,
the only PivotTables on the ActiveSheet?

 

[Jerry Sullivan]

Is there any way you can help me input this into my code? This way I can figure out what I'm doing wrong as I tried to input your code and got the debugger.

I'll post some comments on your existing code in a few minutes to help you learn.

This is how the function I posted earlier would be called to show
only email and fax items on each PivotTable in Sheet "Call Work Code")

Sub ShowEmailFax()
    Dim PT As PivotTable
    For Each PT In Sheets("Call Work Code")
        With PT
            Filter_PivotField _
                pvtField:=PT.PivotFields("Call Work Code"), _
                    varItemList:=Array("MAIL", "FAX")
        End With
    Next PT
End Sub

 

[Jerry Sullivan]

While reworking your existing code, I see that I missed that you have the PivotTables in 3 different worksheets.

This would be how to use the Filter_PivotField function for multiple sheets:

Sub ShowEmailFax2()
    Dim PT As PivotTable
    Dim sMySheets As Variant
    Dim ws As Worksheet
    
    Set sMySheets = Sheets(Array( _
        "Call Work Code", "User.Call Work Code", _
        "Dealer.Call Work Code"))
    For Each ws In sMySheets
        For Each PT In Sheets("Call Work Code")
            With PT
                Filter_PivotField _
                    pvtField:=PT.PivotFields("Call Work Code"), _
                        varItemList:=Array("MAIL", "FAX")
            End With
        Next PT
    Next ws
End Sub

I'll post a rework of your code soon with some comments.

 

[Jerry Sullivan]

Originally Posted by natetc
Is there any way you can help me input this into my code? This way I can figure out what I'm doing wrong as I tried to input your code and got the debugger.

Here is a fix to get your existing code working.
You could repeat the code 3 times with your different Sheets and PivotTable Names.

 Sub natetc()
    Dim i As Long
    On Error Resume Next
    With Sheets("Call Work Code")
        With .PivotTables("PivotTable2").PivotFields("Call Work Code")
            .PivotItems("EMAIL").Visible = True
            .PivotItems("FAX").Visible = True
            For i = 1 To .PivotItems.Count
                If .PivotItems(i) <> "EMAIL" And _
                    .PivotItems(i) <> "FAX" And _
                      .PivotItems(i).Visible = True Then
                    .PivotItems(i).Visible = False
                End If
            Next i
        End With
    End With
End Sub

A few notes:
1. You don't have to Select each Sheet to use it. It's better to just reference the Sheet by its name.
2. Your code was setting PivotItems("EMAIL").Visible = FALSE. It should be set to TRUE since you want it to be visible.
3. By setting On Error Resume Next your code will continue if PivotItems EMAIL or FAX don't exist. (VoG helped you with this technique in a recent post).
4. Your code didn't have anything in it to set the other PivotItems to Visible=FALSE.
5. If neither EMAIL nor FAX exists, through VBA you have to keep at least one item visible (you can't show no items in that case).

Hope this helps.