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

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!!

Scott Holtzman Scott Holtzman · Accepted Answer · 2012-06-22T20:01:19

Unfortunatley, you cannot apply to of the same type of filters to one field in a PT. This link is a decent read on that.

To solve your problem you can can replace this block of code in your post above:

Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.AutoSort xlAscending, "Resource name"

With the below code:

'1) Filter on any thing that contains `TBD`
Set objField = objTable.PivotFields("Resource name")
objField.Orientation = xlRowField
objField.Position = 5
objField.PivotFilters.Add xlCaptionContains, Value1:="TBD"

'2) Loop through the items in the field and uncheck anything with `ATG` in the name
Dim pi As PivotItem
Dim i as Integer

For i = 1 To objField.PivotItems.Count
    If InStr(1, objField.PivotItems(i), "ATG") <> 0 Then
        objField.PivotItems(i).Visible = False
    End If
Next

objField.AutoSort xlAscending, "Resource name"

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

1 Answers