Pivot Table Macro issue

N

natetc

Board Regular
Joined
Oct 2, 2008
Messages
127
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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.

Rich (BB code): 
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.
Rich (BB code): 
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
Rich (BB code): 
Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Locations"), _
        varItemList:=Application.Transpose(Sheets("Sheet2").Range("Cities"))
End Sub

Hope this helps!
 
Upvote 0
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.
 
Upvote 0
natetc said:
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.
Click to expand...

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?
 
Upvote 0
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!
 
Upvote 0
natetc said:
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.
Click to expand...

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")

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
 
Upvote 0
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:

Code: 
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.
 
Upvote 0
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.
Click to expand...

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

Code: 
 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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

SandsB
  • Question Question
Chart Type changes when updated
Replies
0
Views
67
SandsB
SandsB
C
  • Solved
Change pivot field with a variable in VBA
Replies
4
Views
164
cogswel__cogs
C
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
188
Anthony47
A
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
845
howard
H
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
243
Jette3173
J

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top