none
Filtering Issues with Macro for Creating Pivot Table in Excel 2010 VBA RRS feed

  • Question

  • I am new to VBA and am trying to write a macro that will create a pivot table. I need to filter various fields and have tried using PivotFilters.Add and PivotItems to only let certain things through...sometimes it works, but other times it throws errors. The following code works just fine:

    Sub CreatePivot()
        Dim objTable As PivotTable, objField As PivotField
        
        ActiveWorkbook.Sheets("CP Monthly Data").Select
        Range("A1").Select
        Set objTable = Sheet1.PivotTableWizard
        objTable.Name = "Resource Requests"
        objTable.InGridDropZones = True
        objTable.RowAxisLayout xlTabularRow
        
        Set objField = objTable.PivotFields("Company name")
        objField.Orientation = xlRowField
        objField.Position = 1
        
        Set objField = objTable.PivotFields("Probability Status")
        objField.Orientation = xlRowField
        objField.Position = 2
        objField.PivotItems("X - Lost - 0%").Visible = False
        objField.PivotItems("X - On Hold - 0%").Visible = False
        objField.AutoSort xlDescending, "Probability Status"
        
        Set objField = objTable.PivotFields("Project")
        objField.Orientation = xlRowField
        objField.Position = 3
        
        Set objField = objTable.PivotFields("Project manager")
        objField.Orientation = xlRowField
        objField.Position = 4
        
        Set objField = objTable.PivotFields("Resource name")
        objField.Orientation = xlRowField
        objField.Position = 5
        objField.AutoSort xlAscending, "Resource name"
        
        Set objField = objTable.PivotFields("June, 2012")
        objField.Orientation = xlDataField
        objField.Function = xlSum
        objField.NumberFormat = "##"
        objField.Caption = "June"
        
        Set objField = objTable.PivotFields("Workgroup Name")
        objField.Orientation = xlPageField
        objField.PivotItems("ATG").Visible = False
        objField.PivotItems("India - ATG").Visible = False
        objField.PivotItems("India - Managed Middleware").Visible = False
    
    
        Application.DisplayAlerts = True
    End Sub
    

    The "Resource name" field is giving me problems. I need to only show the resource names that begin with "*TBD" and exclude those that contain "ATG" in the name. I have so far tried the following:

        Set objField = objTable.PivotFields("Resource name")
        objField.Orientation = xlRowField
        objField.Position = 5
        objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"
        objField.PivotFilters.Add xlCaptionDoesNotContain, Value1:="ATG"
        objField.AutoSort xlAscending, "Resource name"

    Which returns "Run-time error '1004': Application-defined or object-defined error

    This isn't exactly what I need since I also need to filter out those without "TBD" in the name, but I have also tried:

        Set objField = objTable.PivotFields("Resource name")
        objField.Orientation = xlRowField
        objField.Position = 5
        objField.PivotItems("*ATG*").Visible = False
        objField.AutoSort xlAscending, "Resource name"

    Which returns "Run-time error '1004': Unable to get the PivotItems property of the PivotField class

    I have also tried recording a macro and checking the results against my code. The results use PivotFilters.Add which I tried. The main difference between the recorded macro and my code is the use of PivotTableWizard and I'm starting to wonder if that matters...I'm new, remember?

    Any ideas on how to fix this? I'm using Excel 2010 and have spent hours searching for this, and nothing I've tried has worked. Thanks in advance for any help!!

    Friday, June 22, 2012 6:27 PM

All replies

  • 1.

     objField.PivotItems("*ATG*").Visible

    This type of access is not alloowable in VBA.

    2.

    One rowfield can have only filter applied (Lable/Value).objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"
        objField
    .PivotFilters.Add xlCaptionDoesNotContain

    So your requirement can not be fulfilled with PivotFilters.You need to check each item and see if your condition matches or not.I have given a block which you can use.

    Dim objItem As PivotItem
        
        For Each objItem In objField.PivotItems
            If (Right(objItem.Value, 3) = "TBD" Or _
                Not (objItem.Value Like "*ATG*")) Then
                'Check the logic of above condition and
                'make necessary adjustment if reqd.
             objItem.Visible = False
             'Take note that
             'If all of your item does match
             'then error will be raised by VBA
             
            End If
        Next objItem

    ---------------------------------------------------------------------------------------------

    Please do not forget to click “Vote as Helpful” if any post helps you and mark as Answer if it solves the issue.

    Saturday, June 23, 2012 9:37 AM
    Answerer